How to Invert Cell and Filter Selection in Excel

Do you ever find the need to delete or hide every cell except the current selection? 

Well, with the inverted selection, you can easily discard the selected ranges and select the remaining cells. 

Although there’s no built-in menu for Invert Selection, we can achieve that using the VBA code. Here, I will guide you on how to Invert Cell and Filter Selection.

Invert Cell Selection

Suppose, I have selected a few cell ranges. Let’s invert this cell selection.

1. On your sheet press Alt + F11 keyboard shortcut. 
2. Expand Insert Tab > Module.
   
3. Copy-paste the given VBA code.
   

Sub Invert_Selection()
Dim R As Range
Dim R1 As Range
Dim R2 As Range
Dim OuR As Range
Set R1 = Application.Selection
Set R1 = Application.InputBox("Selected Range :", xTitleId, R1.Address, Type:=8)
Set R2 = Application.InputBox("Select New Range", xTitleId, Type:=8)
For Each R In R2
    If Application.Intersect(R, R1) Is Nothing Then
        If OuR Is Nothing Then
            Set OuR = R
        Else
            Set OuR = Application.Union(OuR, R)
        End If
    End If
Next
OuR.Select
End Sub

4. Now, hit Run button.
   
5. You’ll see the currently selected ranges in the dialogue box. Hit OK to continue.
   
6. Then, on the next Select New Range window, select Cell Ranges and click OK.
   

Invert Filter Selection

Next, when you apply a Filter in the column, it shows the selected lists and hides the rest of the items. But what if you want to show the hidden ones instead and do the Invert selection? 

Unlike cell selection, you can easily invert the filter by unticking and re-ticking the lists. But, that’d be so much tedious work for large items.

To ease your process, we will add a custom sub-command named “Invert Filter Selection” command in the Filter’s context menu.

Step 1: Create Personal XLSB

1. On your Sheet, head to the Developer Tab and click on Visual Basic.
2. Double-click on ThisWorkbook. Then, paste the copy-paste the given code.
   

Private Sub Workbook_Open()
Windows(“Personal.xlsb”).Visible = False
Call AddToCellMenu
End Sub

3. Hit Save.
   

Step 2: Add Invert Filter Command in Context Menu

1. On the same VBA window, click Insert > Module.
   
2. Then, copy and paste the Public Sub AddToCellMenu Code from StackOverflow onto your Module. Either hit Run. Or, press F5 key.

To use it, make sure you have filtered the items.  Now, when you right-click on the cell and choose Filter, you can see the Invert Filter Selection Menu.