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
Else
Set rNew = Union(rNew, cell)
End If
End If
Next cell
End If
If Not rNew Is Nothing Then
rNew.Select
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.
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.
Thanks
Dennis
Dennis
Try deleting and re-typing the double quotes around the word range in the first If statement.
John
I have downloaded a report that shows negative numbers as follows. 123.25-
How can I make the negative symbol appear on the left.
Roberto
One way you could fix this would be with a formula like the following one.
=-1*LEFT(A1,LEN(A1)-1)
John
Hi John
I want to invert the numbers in excel. i.e
column is
1234567
8901234
what I want is
7654321
4321098
Hope you can help me with this..