How to Deselect in Excel VBA

Sometimes a user might need to deselect a cell, range, or object to remove focus from a particular cell, range, or object after performing a task or to clear selection to perform other operations. In this article, we will demonstrate various ways to deselect in Excel VBA like cells, ranges, etc.

How to Launch VBA Editor in Excel

To access the Microsoft Visual Basic window, go to the Developer tab and then click on Visual Basic. You can also open it by pressing Alt+F11 on your keyboard.

Then go to the Insert tab and click on Module to open the code Module.

How to Deselect in Excel VBA: 7 Suitable Ways

In this article, we will show you seven ways to deselect cells, ranges, etc in Excel Using VBA. We will use the following dataset for this purpose.

1. Use CutCopy Mode to Deselect

In the first method, we will use the CutCopy mode to deselect a selected range of cells. The procedure to do so is discussed below.

• First of all, go to Microsoft Visual Basic code Module and write the following code there.

Sub SelectCutCopyMode()
Dim SelectedCell As Range
Set SelectedCell = Range("E1")
SelectedCell.Select
Application.CutCopyMode = False
Columns(SelectedCell.Column).Hidden = True
End Sub

Set SelectedCell = Range("E1")
SelectedCell.Select
Application.CutCopyMode = False
Columns(SelectedCell.Column).Hidden = True

• Then select a range that you need to deselect.

• After that, press F5 to run the code.
• As a result, cell E1 will be selected and column E will be hidden.

2. Deselect Using the .Protect Property

In this method, we will use the .Protect property in VBA to get nothing selected in our worksheet. We will use the following steps to achieve this goal.

• Select any cell or range in your worksheet.

• Then open the VBA code Module and paste the following code in there.

Sub DeselectProtect()
Dim ws As Worksheet
Set ws = ActiveSheet
With ws
.EnableSelection = xlNoSelection
.Protect
End With
End Sub

Set ws = ActiveSheet
With ws
.EnableSelection = xlNoSelection
.Protect
End With

• Finally, run the code and you will see that no cells are selected. In the following figure, the active cell is B4 which is not selected.

3. Select Another Cell to Deselect a Specific Range or Column

In this method, we will select another cell to deselect a specific range or column. We will select cell A1 in this example as it is outside our selected range or column. You can select any other cell as per your need. The steps to do so are given below.

• First, write the following code in the VBA Module.

Sub SelectAnotherCell()
ThisWorkbook.Sheets("Another").Range("A1").Select
End Sub

ThisWorkbook.Sheets("Another").Range("A1").Select

• Here, B4 to B12 is our selected range. We want to select a cell outside this range to deselect this range.

• Run the VBA code and the range B4:B12 is no longer selected. Instead, cell A1 is now selected.

Read More: Excel VBA to Select First Visible Cell in Filtered Range

4. Remove Active Cells from Selection

In this example, we have a range selected in our worksheet where the active cell is the first cell of the range. We want to remove the active cell from the selected range now. We will use the following VBA code for this purpose.

Sub DeselectActiveCell()
    Dim rng As Range
    Dim rngSelected As Range 
    For Each rng In Selection.Cells
        If StrComp(rng.Address, ActiveCell.Address, _
        vbBinaryCompare) <> 0 Then
            If rngSelected Is Nothing Then
            Set rngSelected = rng
            Else
            Set rngSelected = Application.Union(rngSelected, rng)
            End If
        End If
    Next rng  
    If Not rngSelected Is Nothing Then
        rngSelected.Select
    End If   
End Sub

For Each rng In Selection.Cells
        If StrComp(rng.Address, ActiveCell.Address, _
        vbBinaryCompare) <> 0 Then
            If rngSelected Is Nothing Then
            Set rngSelected = rng
            Else
            Set rngSelected = Application.Union(rngSelected, rng)
            End If
        End If
    Next rng

Here, the selected range is B4:D12 and the active cell is B4. We want to remove the active cell using the code.

To do so, run the VBA code and you will find that the new active cell is C4.

5. Use Selection.Locked to Deselect

We can also deselect a range using Selection.Locked. We will use the VBA code given below for this purpose.

Sub DeselectLocked()
    Range("E1").Select
    Selection.Locked = True
End Sub

 Range("E1").Select
    Selection.Locked = True

The range we want to deselect is B4:B12 in this example.

Press F5 to run the VBA code to deselect the specified range.

6. Deselect Specific Cells from a Selection

In this example, we have a range of cells selected. From the range of cells, we want to deselect some specific cells. We will use the following steps to do so.

• First of all, write the following code in the VBA code Module.

Sub DeselectSpecificCell()
    Dim rng As Range
    Dim selectedRange As Range
    Dim deleteRange As Range
    Dim remainingRange As Range
    Dim xTitleId As String    
    xTitleId = "Deselect Cells"    
    Set selectedRange = Application.Selection
    Set selectedRange = Application.InputBox("Select Range :", _
    xTitleId, selectedRange.Address, Type:=8)    
    Set deleteRange = Application.InputBox("Deselect Cells", _
    xTitleId, Type:=8)    
    For Each rng In selectedRange
        If Application.Intersect(rng, deleteRange) Is Nothing Then
            If remainingRange Is Nothing Then
            Set remainingRange = rng
            Else
            Set remainingRange = Application.Union(remainingRange, rng)
            End If
        End If
    Next  
    If Not remainingRange Is Nothing Then
        remainingRange.Select
    End If
End Sub

 For Each rng In selectedRange
        If Application.Intersect(rng, deleteRange) Is Nothing Then
            If remainingRange Is Nothing Then
            Set remainingRange = rng
            Else
            Set remainingRange = Application.Union(remainingRange, rng)
            End If
        End If
    Next

• Then run the code by pressing the F5 on your keyboard.
• As a result, an Input Box will appear where you need to insert the whole range. We have selected the range B4:B12.
• After that, click on OK.

• Then another Input Box will pop up, where you need to input the cells you want to deselect. Then press OK.

• As a result, the specified cells will be deselected.

7. Select a Cell Outside Visible Range

In this method, the VBA code will select a cell that is outside the visible range. In the following figure, the visible range of the worksheet is up to the 17th row.

To select a cell outside this range, we will use the code given below.

Sub DeselectOutsideRange()
Dim rng As Range
Application.ScreenUpdating = False
Set rng = Application.ActiveWindow.VisibleRange
rng(rng.Cells.Count + 1).Select
Application.ScreenUpdating = True
End Sub

Application.ScreenUpdating = False
Set rng = Application.ActiveWindow.VisibleRange
rng(rng.Cells.Count + 1).Select
Application.ScreenUpdating = True

Run the code and you will find that the newly selected cell is A18 which is the next cell after the last visible cell.

Read More: How to Select Visible Cells in Excel with VBA

How to Deselect Shape in Excel VBA

In this example, we will deselect a shape in an Excel worksheet using VBA. We will use the VBA code given below.

Sub DeselectShape()
    Dim ws As Worksheet
    Dim rng As Range
    Dim cell As Range  
    Set ws = ThisWorkbook.Sheets("Shape") '
    Set rng = ws.Range("D5:D12")  
    For Each cell In rng      
        If cell.Value > 5000 Then
            cell.Interior.Color = RGB(0, 255, 0)
        End If
    Next cell  
    On Error Resume Next
    ws.Shapes(Application.Caller).Select (False)
    On Error GoTo 0
    ActiveSheet.Range("A1").Select  
End Sub

   ws.Shapes(Application.Caller).Select (False)
    On Error GoTo 0
    ActiveSheet.Range("A1").Select

In the following figure, the rectangular shape is selected. We want to deselect this shape using the above code.

Run the code and go back to the Excel sheet. You will find that the shape is deselected and cell A1 is selected.

Frequently Asked Questions

How to deselect a range after copy-paste?

Typically, when using VBA code to copy and paste a specific range in Excel, the range is not automatically selected. However, if it is selected, you can use any of the methods mentioned above to deselect the range.

Things to Remember

• The first method is convenient as it selects a cell outside the specified range and then hides it.
• The second method is the best way to deselect cells in Excel.
• Remember to carefully consider the specific requirements of your task and choose the most appropriate method for deselecting cells or ranges in Excel VBA.

Download Practice Workbook

Download this practice workbook to exercise while reading this article.

Conclusion

Thanks for making it this far. I hope you found this article useful. In this article, we have demonstrated seven methods on how to deselect in Excel VBA like cells, ranges, etc. We have explored CutCopy mode to remove active cells, to utilize the Selection.Locked property, and deselecting specific cells or ranges for this purpose. If you have any queries or recommendations regarding this article, feel free to let us know in the comment section below.

Related Articles

• Excel VBA: Select All Cells with Data
• Excel VBA: Select Visible Cells After Autofilter