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
Code Breakdown
Set SelectedCell = Range("E1")
SelectedCell.Select
Application.CutCopyMode = False
Columns(SelectedCell.Column).Hidden = True
- The code declares a variable named SelectedCell and sets the cell E1 to it.
- Application.CutCopyMode = False line sets the CutCopyMode to False.
- The last line hides the column containing cell E1.
- 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
Code Breakdown
Set ws = ActiveSheet
With ws
.EnableSelection = xlNoSelection
.Protect
End With
- The code calls the Protect method to protect the active worksheet. This prevents changes to the worksheet.
- 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
Code Breakdown
ThisWorkbook.Sheets("Another").Range("A1").Select
- This code selects the cell A1 of the worksheet named Another.
- 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
Code Breakdown
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
- The code starts a loop that iterates over selected cells.
- Â If StrComp(rng.Address, ActiveCell.Address, vbBinaryCompare) <> 0 line compares the address of the current cell with the address of the active cell.
- Set rngSelected = rng sets the rngSelected variable to the current cell, if it is the first cell that meets the criteria.
- Set rngSelected = Application.Union(rngSelected, rng) line uses the Union method of the Application object to combine the current cell with the previously selected cells.
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
Code Breakdown
 Range("E1").Select
    Selection.Locked = True
- The code first selects cell E1 in the active worksheet.
- Then it locks the selected cell by 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
Code Breakdown
 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
- This code initiates a loop that iterates through the selected range.
- Â Â If Application.Intersect(rng, deleteRange) Is Nothing Then the line checks if the current cell intersects with other cells of the range named deleteRange.
- If it does, the cells are deselected. The cells which are not intersected are stored in remainingRange.
- Set remainingRange = Application.Union(remainingRange, rng) uses the Application.Union method to combine the existing remainingRange with the current cell after each loop.
- 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
Code Breakdown
Application.ScreenUpdating = False
Set rng = Application.ActiveWindow.VisibleRange
rng(rng.Cells.Count + 1).Select
Application.ScreenUpdating = True
- The code sets the ScreenUpdating to False to disable screen updating.
- Set rng = Application.ActiveWindow.VisibleRange sets the range to the visible range of the worksheet.
- rng(rng.Cells.Count + 1).Select selects the next cell after the last visible cell in column A.
- The last line allows the screen to update normally after executing the code.
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
Code Breakdown
   ws.Shapes(Application.Caller).Select (False)
    On Error GoTo 0
    ActiveSheet.Range("A1").Select
- The code first selects a shape on a worksheet.
- Application.Caller property returns the name or reference of the object that is called the currently executing macro.
- ActiveSheet.Range(“A1”).Select selects cell A1 in the active worksheet.
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.