Clear Selection in VBA

As you are probably aware by now, Visual Basic for Application (VBA) has virtually limitless options for automating Office files, especially Excel.

When writing our code, there are several things that we can do to clean and speed things up. In the example below, we will show how can we clear selection in VBA, whether it is a left-out selection after copying and pasting data or simply clearing a certain selected range.

Clearing Selection When Copying

For our example, we will use a random set of numbers, and we will put the numbers in column A:

Table

Description automatically generated with medium confidence

The goal now is to copy this data (located in Sheet1), to a different sheet (the sheet that we will name Sheet2). To do this, we will open the VBA by pressing ALT + F11 on our keyboard, and then we will right-click anywhere on the left side of the window that opens, and choose Insert >> Module:

Graphical user interface, application

Description automatically generated

When we do this, we will have a new window opened on the right side. There, we will insert the following code to copy and paste data from Sheet1 to Sheet2:

Sub Copy_Paste()
Sheets("Sheet1").Activate
Range("A1:A13").Copy
Sheets("Sheet2").Activate
Range("A1").PasteSpecial
End Sub

When we execute this code by pressing F5 on our keyboard while in the module, our data will be copied in Sheet2:

Table

Description automatically generated with medium confidence

When we return to Sheet1 now, we will notice that, after the code execution, our data in Sheet1 is still selected:

A screenshot of a computer

Description automatically generated with medium confidence

Luckily, there is a great option to clear this selection. This part of the code not only removes this issue but also speeds up our code, which is especially helpful if we have a large code.

All we need to do is to insert the following line at the beginning of our code:

Application.CutCopyMode = False

This instruction makes sure that selection is removed when data is copied or cut. As this line of code relates to the application (Excel) as a whole, we need to make sure to revert this when we finish executing the code. For this, we only need to add:

Application.CutCopyMode = True

At the end of the code. This is our code now:

Sub Copy_Paste()
Application.CutCopyMode = False
Sheets("Sheet1").Activate
Range("A1:A13").Copy
Sheets("Sheet2").Activate
Range("A1").PasteSpecial
Application.CutCopyMode = True
End Sub

And this is what it looks like in our module:

Graphical user interface, text, application, email

Description automatically generated

When we execute the code again, we will not have data in Sheet1 selected any longer:

A screenshot of a computer

Description automatically generated with medium confidence

Clearing Selected Range

Apart from clearing the selection when copying, we can also clear any range, by selecting it and then clearing it. The code for this is pretty easy. Let us suppose that we have random numbers in column B (range B2:B10):

Table

Description automatically generated

This will be our code to clear said data:

Sub Clearing_Data()
Sheets("Sheet1").Select
Range("B2:B10").Select
Selection.Clear
End Sub

In the module, the code is as follows:

Graphical user interface, text, application, email

Description automatically generated

Once we execute this code, we will not have the data in range B2:B10.

Posted in vba