Excel VBA to Cancel Selection (5 Suitable Examples)

While we work with datasets in Excel, we often need to cancel the current selection for further data calculation. We can cancel any existing selection by mouse-clicking quite easily. But, in this article, we’ll show you how to use Excel VBA to cancel selection.

We know that almost every task in Excel can be done with the help of Visual Basic for Applications (VBA) code. So, canceling any selection can be done with the VBA code also.

In this article, we will hover around the cancellation of selection in Excel with the help of the VBA code.

In addition, we will also show the ways to select multiple cells and clear their contents utilizing VBA code.

How to Launch VBA Editor in Excel

For inserting any VBA code, open the VBA window first. For that, just click on the Developer tab and select Visual Basic from the appeared options. Also, you can open the VBA window by pressing the keyboard shortcut Alt + F11.

If your Excel doesn’t have the Developer tab automatically, then you can display the Developer tab in Excel first.

After opening the VBA window, you need to Insert a new Module.

For running any code, just press the Run button from the VBA window, as in the screenshot. Also, you can press the keyboard shortcut F5.

Excel VBA to Cancel Selection: 5 Suitable Examples

Here, we will discuss 5 different ways to cancel any selection in Excel. Each method has some benefits and drawbacks. Go through the methods for a better understanding.

1. Using VBA Application.CutCopyMode Property to Cancel Selection While Copying

When we copy the values from some range in Excel with VBA and paste them in another location after the task is done, the range being used remains selected normally, which may hamper our further procedures. For this, we will need to deselect that selection. Let’s see a simple solution for that.

Just insert the attached code into a new module in the VBA window and run it.

Code:

Sub Calcel_Selection_Copying()
'Calcelling selection after copying
Application.CutCopyMode = False
'Activating sheet and copying range
Sheets("1.Calcel_Selection_Copying").Activate
Range("B5:D10").Copy
'Pasting the copied values
Range("B11:D16").PasteSpecial
'Again enabling selection
Application.CutCopyMode = True
End Sub

Follow the video for better clarification.

2. Changing Worksheet.EnableSelection Property to Cancel Selection in Excel

Another way to cancel any selection is to change the Worksheet.EnableSelection property in the VBA code and protect the worksheet. This will disable the worksheet for any further selection or change.

Put the following code in a new module and run it.

Code:

Sub Enable_Selection_Property()
With ActiveSheet
'disapling any range in worksheet for cancelling any selection
.EnableSelection = xlNoSelection
.Protect
'You can enable selection in worksheet again with following lines activated
'.EnableSelection = xlYesSelection
'.Unprotect
End With
End Sub

In the code, we just set the EnableSelection property to xlNoSelection and protected the sheet with .Protect command which cancels any further selection and protects the sheet from any change. You can enable the last two comments in the code to enable the sheet for selection and change.

Watch the video for a better understanding.

3. Deselecting Active Cell of Current Selection with Excel VBA

This method will demonstrate the deselection of the active cell from the current selection. All other cells of the selection will remain selected except the active cell.

Apply the attached code in a new module and run it.

Code:

Sub ActiveCell_Deselect()
Dim Rng As Range
Dim Rng_R As Range
'Doing for loop for deselecting active cell
For Each Rng In Selection.Cells
If StrComp(Rng.Address, ActiveCell.Address, vbBinaryCompare) <> 0 Then
If Rng_R Is Nothing Then
Set Rng_R = Rng
Else
Set Rng_R = Application.Union(Rng_R, Rng)
End If
End If
Next Rng
If Not Rng_R Is Nothing Then
Rng_R.Select
End If
End Sub

In the code, we did a for loop for all combining all the cells of selection except the active cell. Later on, selected the combination.

For convenience, watch the video.

4. Cancelling Selection of Specific Cells Within Selected Ranges

If we want to deselect a few continuous or noncontinuous cells inside the current selection, that can be done quite simply with VBA code. Let’s see the way with VBA.

Just write the following code in a new module and run it.

Code:

Sub DeSelect_Specific_Cells()
Set Rng = Application.Selection
Set Rng = Application.InputBox("Select specific Range:", "DeSelectCells", _
Rng.Address, Type:=8)
Set Delete_Rng = Application.InputBox("select the range of cells to deselect", _
"DeSelect Cells", Type:=8)
Dim Last_Range As Range
'Doing For loop for deselecting cells
For Each myCell In Rng
If Application.Intersect(myCell, Delete_Rng) Is Nothing Then
If Last_Range Is Nothing Then
Set Last_Range = myCell
Else
Set Last_Range = Application.Union(Last_Range, myCell)
End If
End If
Next
Last_Range.Select
End Sub

Watch the video for convenience.

5. Excel VBA for Selecting New Range to Cancel Previous Selection

Now, let’s see the simplest solution to our problem. We will just select another cell from our worksheet, which will automatically cancel our previous selection. Without further delay, let’s see the procedures.

Insert the following code in a new module and run it.

Code:

Sub Remove_Previous_Selection()
Worksheets("5.Remove_Previous_Selection").Range("B5").Select
End Sub

In the code, we just selected range B5 of our worksheet with .Select command, which cancels our previous selection automatically.

The video demonstrates our method, watch it.

How to Clear Contents in Excel VBA

So far, we’ve discussed the ways to cancel any previous selection in our worksheet with the help of VBA. Now, let’s see the method to clear the content of a cell or range of cells with a short piece of VBA code. The code will remove any value from the selected range but will keep the format intact.

Apply the following code in a new module and run it.

Code:

Sub Clear_Contents()
Worksheets("Clear_Contents").Range("B11:D13").ClearContents
End Sub

In the code, the .ClearContents command deletes values from the defined range but keeps the format intact.

After running the code, you will see a change like in the attached image.

How to Clear All Cell Properties with Contents in Excel VBA

Of course, there’s a way to clear the format along with cell values from a range. Let’s discuss how to do that with VBA.

Code:

Sub Clear_Cell_Properties()
Worksheets("Clear_Cell_Properties").Range("B11:D13").Clear
End Sub

In the code, the .Clear command deletes the values from the defined range along with formats.

You will see a result like in the image after running the code.

How to Select Multiple Cells in Excel with VBA

It’s too simple to select multiple cells by pressing Ctrl and selecting the cells with a mouse click. But we may need to do it with VBA for further use of those cells. Let’s see how to do that.

Insert the following code in a new module and run it.

Code:

Sub Select_Multiple_Cells()
Worksheets("Select_Multiple_Cells").Range("B" & 10 & ",C" & 10 & ",D" & 10).Select
End Sub

In the code, we have used the .Select property with Range. The arguments inside the range are given as strings and numbers combined with “&”. You can add more cells in a similar fashion.

After running the code, you will see that multiple cells from your worksheet got selected, like in the image above.

Frequently Asked Questions

Q: What is the difference between Clear and Deselect?

A: When we select a range of cells from our worksheet, deselecting cells means removing some cells or all cells from the selection. We can do that by pressing Ctrl and clicking on the cells under the selection to unselect them. Whereas clear cells mean to eradicate their cell values. We may keep the format or remove it as well.

Q: How can I cancel a selection in Excel VBA?

A: You can cancel a selection in Excel VBA by setting the selection to a different cell or range of cells by dint of the Range object of VBA.

Q: Can I cancel a selection without selecting a new cell?

A: No, you cannot cancel a selection without selecting a new cell or range of cells because Excel always needs a cell or range of cells selected by default. The Select method is the only way to change the selection in VBA.

Q: What happens if I don’t cancel a selection before running another macro or command?

A: If you don’t cancel a selection before running another macro or command, the new macro or command will operate on the selected cells instead of the intended range. This can cause unexpected results or errors in your code.

Q: How can I check if there is currently a selection in Excel VBA?

A: You can check if there is currently a selection in Excel VBA by using the Selection object.

Things to Remember

• In a few methods, we used worksheet names in the code you have to change them according to your one.
• Give special focus when dealing with cell references in the code. Sometimes, you have to modify the cell reference in the codes.
• Don’t forget to save the workbook as the xlsm file before running the code.

Wrapping Up

• In the first method, we have discussed the way to cancel the selection when we copy a cell range with VBA.
• Then, introduce a method to protect the sheet from any further selection or change.
• Later on, we showed the methods to dissect active and specific cells respectively from our current selection utilizing VBA.
• Further, showed the simplest method to cancel the previous selection by just selecting a new cell.
• In addition, have explained the ways to clear the contents with and without format. Also, discussed the way to select multiple cells with VBA.

Download Practice Workbook

You can download the practice workbook from here.

Conclusion

Selection and deselection are quite common tasks in Excel that are utilized for further processing of the data. In this article, we discussed various methods to use Excel VBA to cancel selection. Each method has its own benefits and drawbacks, as discussed in the methods. Use them according to your preferences. Don’t hesitate to comment if you have any suggestions or queries.

Related Articles

• Excel VBA to Select Used Range in Column
• Excel VBA: Select Range with Offset Based on Active Cell
• How to Use VBA to Select Range from Active Cell in Excel
• How to Select Range Based on Cell Value VBA