Excel VBA to Cancel Selection: 5 Suitable Methods

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

When you copy the values from some range in Excel with VBA and paste them in another location after the task is done, the range used remains selected typically, which may hamper our further procedures. De-select that selection.

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

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.

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

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

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 allow the sheet to be selected and changed.

Watch the video for a better understanding.

Method 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

Did a loop to combine all the cells of selection except the active cell. We selected the combination.

For convenience, watch the video.

Method 4 – Cancelling Selection of Specific Cells Within Selected Ranges

If we want to de-select a few continuous or noncontinuous cells inside the current selection, do so with the VBA code.

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.

Method 5 – Excel VBA for Selecting New Range to Cancel Previous Selection

See the simplest solution to our problem. We will just select another cell from our worksheet, automatically canceling 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, selected range B5 of our worksheet. Select command, which cancels our previous selection automatically.

The video demonstrates our method; watch it.

How to Clear Contents in Excel VBA

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

Code:

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

The ClearContents command deletes values from the defined range but keeps the format intact.

You will see a change in the attached image.

How to Clear All Cell Properties with Contents in Excel VBA

Clear the format along with cell values from a range. 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 and formats from the defined range.

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

How to Select Multiple Cells in Excel with VBA

Selecting multiple cells by pressing Ctrl and selecting them with a mouse click is too simple. But we may need to do it with VBA to use those cells further.

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

We used the Select property with Range. The arguments inside the range are given as strings and numbers combined with “&”. 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. In contrast, clear cells mean eradicating 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 it to a different cell or range of cells using 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 particular 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.
• Introduce a method to protect the sheet from any further selection or change.
• We showed the methods to dissect active and specific cells from our current selection utilizing VBA.
• Further, it showed the simplest method to cancel the previous selection by selecting a new cell.
• In addition, I 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.

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