How to Deselect in Excel VBA

Get FREE Advanced Excel Exercises with Solutions!

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.

Go to Microsoft Visual Basic Application

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

Insert a code Module in VBA


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.

How to Deselect in Excel VBA


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

Use CutCopy Mode to Deselect in Excel VBA

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.

Use CutCopy Mode to Deselect in Excel VBA

  • 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.

Use Protect Property to Deselect in Excel VBA

  • 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

Use Protect Property to Deselect in Excel VBA

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.

Use Protect Property to Deselect in Excel VBA


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

Select Another Cell to Deselect in Excel VBA

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.
  • Select Another Cell to Deselect in Excel VBARun the VBA code and the range B4:B12 is no longer selected. Instead, cell A1 is now selected.

Select Another Cell to Deselect in Excel VBA

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

Remove Active Cells from Selection to Deselect in Excel VBA

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.

Remove Active Cells from Selection to Deselect in Excel VBA

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

Remove Active Cells from Selection to Deselect in Excel VBA


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

Use Selection.Locked to Deselect in Excel VBA

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.

Use Selection.Locked to Deselect in Excel VBA

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

Use Selection.Locked to Deselect in Excel VBA


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

Deselect Specific Cells from Selection

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.

Deselect Specific Cells from Selection

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

Deselect Specific Cells from Selection

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

Deselect Specific Cells from Selection


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.

Select a Cell Outside Visual Range

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

Select a Cell Outside Visual Range

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.

Select a Cell Outside Visual Range

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

How to Deselect Shape in Excel VBA

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.

How to Deselect Shape in Excel VBA

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

How to Deselect Shape in Excel VBA


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

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Alif Bin Hussain
Alif Bin Hussain

Alif Bin Hussain earned a BSc in Civil Engineering from Bangladesh University of Engineering and Technology. As an engineering graduate, he has a deep passion for research and innovation. He loves to play with Excel. In his role as an Excel & VBA Content Developer at ExcelDemy, he not only solves tricky problems but also shows enthusiasm and expertise in handling tough situations with finesse, emphasizing his dedication to delivering top-notch content. He is interested in C, C++,... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo