Inverting The Selection

Here’s a macro that will invert the selection. That is, it will select every cell in the used range except those cells that are currently selected.

Sub InvertSelection()
    Dim rBig As Range
    Dim rSmall As Range
    Dim cell As Range
    Dim rNew As Range
    Set rBig = Sheet1.UsedRange
    If TypeName(Selection) = “Range” Then
        Set rBig = Selection.Parent.UsedRange
        Set rSmall = Selection
    End If
    If Not rSmall Is Nothing Then
        For Each cell In rBig.Cells
            If Intersect(cell, rSmall) Is Nothing Then
                If rNew Is Nothing Then
                    Set rNew = cell
                    Set rNew = Union(rNew, cell)
                End If
            End If
        Next cell
    End If
    If Not rNew Is Nothing Then
    End If
End Sub

With a used range of A32:I56 and the range C45:E50 selected, the result looks like this


Update: Changed the code above to use the active sheet instead of Sheet1. Thanks to Matt for catching that error.

Posted in Uncategorized

5 thoughts on “Inverting The Selection

  1. I have a worksheet named ORDERS.
    I have pasted your code into a module.
    I have selected b2 thru c10.
    I execute your macro and it does nothing.
    I step-into your macro and each If statement is false and does not perform any of the steps before it ends the sub.
    I would find this macro extremely useful and would like to get it to work.
    Please tell me what I am doing wrong.


  2. Dennis

    Try deleting and re-typing the double quotes around the word range in the first If statement.


  3. I have downloaded a report that shows negative numbers as follows. 123.25-
    How can I make the negative symbol appear on the left.

  4. Roberto

    One way you could fix this would be with a formula like the following one.



  5. Hi John

    I want to invert the numbers in excel. i.e

    column is


    what I want is


    Hope you can help me with this..

Posting code? Use <pre> tags for VBA and <code> tags for inline.

Leave a Reply

Your email address will not be published.