How to invert a selection in Microsoft Excel

[Rounder1]

I have some groups of cells scattered at different parts of the sheet (saying what the cell next to it is) selected. How do I invert my selection so that instead of the "title" cells being selected the "data" cells are. There is no "invert selection" in the edit menu like there is for other programs.

[Gania]

I am assuming that you want to move to the right side rather than to the left:

Sub movsel()
Set r1 = Selection
Set rf = Nothing
For Each r In r1
If rf Is Nothing Then
Set rf = r.Offset(0, 1)
Else
Set rf = Union(rf, r.Offset(0, 1))
End If
Next
rf.Select
End Sub

If you wanted to move to the left use Offset(0,-1) instead.

[Alfons]

Maybe I was a little to vague. Here is the problem, I have a sheet with 16 cells, 4 by 4, and I have 6 cells selected using the control key. They are randomly scattered over the 16. I want to invert so that the 6 cells that were selected are not now selected and the 10 that were not, are selected. They are not always next to each other. There is no menu that I can find that will swap my selection. Is there a hidden menu or combination of keys to do this.

[JustineBB]

This macro does what you want:

Sub jon()
Set r1 = Selection
Set r2 = Range("A14")
Set rinv = Nothing
For Each r In r2
If Intersect(r, r1) Is Nothing Then
If rinv Is Nothing Then
Set rinv = r
Else
Set rinv = Union(rinv, r)
End If
End If
Next
If rinv Is Nothing Then
Else
rinv.Select
End If
End Sub

It works on the range from A1 through D4. Change this to suit your needs. Macros are very easy to install and use:

1. CNTRL-F11 brings up the VBE window
2. ALT-I / ALT-M opens a fresh module
3. paste the stuff in and close the VBE window

If you save the workbook, the macro will be saved with it. To remove the macro:

1. bring up the VBE window as above
2. clear the code out
3. close the VBE window