How do you invert your selection of cells in excel?
 

I have groups of cells scatered at differnt parts of the sheet (saying what
the cell next to it is) selected. How do in invert my selection so that
instade 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.

Answer: How do you invert your selection of cells in excel?
 

To invert your selection of cells in Excel, you can use the keyboard shortcut "Ctrl + Shift + *". This will select the entire range of cells that are adjacent to the currently selected cells.

Here are the steps to follow:

1. Select the cells that you want to invert the selection for.
2. Press "Ctrl + Shift + *" on your keyboard. This will select all the adjacent cells to the currently selected cells.
3. Press "Ctrl + -" on your keyboard. This will bring up the "Delete" dialog box.
4. Select "Shift cells left" or "Shift cells up" depending on the orientation of your data.
5. Click "OK" to confirm the deletion.

This will delete the cells that were originally selected and leave the cells that were adjacent to them selected instead.

Alternatively, you can also use the "Go To Special" feature to invert your selection. Here's how:

1. Select the cells that you want to invert the selection for.
2. Press "Ctrl + G" on your keyboard to bring up the "Go To" dialog box.
3. Click on the "Special" button at the bottom left corner of the dialog box.
4. In the "Go To Special" dialog box, select "Blanks" and click "OK".
5. This will select all the cells that are not blank in the range, effectively inverting your selection.

How do you invert your selection of cells in excel?
 

I am assuming that you want to move to the right 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.
--
Gary's Student
gsnu200708


"Jon" wrote:

I have groups of cells scatered at differnt parts of the sheet (saying what
the cell next to it is) selected. How do in invert my selection so that
instade 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.

How do you invert your selection of cells in excel?
 

Maybe I was a little to vague. Here is the problem, I have a sheet with 16
cells, 4 by 4, I have 6 cells selected using the control key and clicking.
They are randomly scattered over the 16. I want to invert so that the 6 cells
that were selected are not now 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?

"Gary''s Student" wrote:

I am assuming that you want to move to the right 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.
--
Gary's Student
gsnu200708


"Jon" wrote:

I have groups of cells scatered at differnt parts of the sheet (saying what
the cell next to it is) selected. How do in invert my selection so that
instade 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.

How do you invert your selection of cells in excel?
 

Hi Jon:

This macro does what you want:

Sub jon()

Set r1 = Selection
Set r2 = Range("A1:D4")
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 thru D4. Change this to suit your needs. If
you are not familiar with macros:


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

To learn more about macros in general, see:

http://www.mvps.org/dmcritchie/excel/getstarted.htm

Once the macro has been created you can assign a shortcut key to it.
--
Gary''s Student
gsnu200708


"Jon" wrote:

Maybe I was a little to vague. Here is the problem, I have a sheet with 16
cells, 4 by 4, I have 6 cells selected using the control key and clicking.
They are randomly scattered over the 16. I want to invert so that the 6 cells
that were selected are not now 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?

"Gary''s Student" wrote:

I am assuming that you want to move to the right 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.
--
Gary's Student
gsnu200708


"Jon" wrote:

I have groups of cells scatered at differnt parts of the sheet (saying what
the cell next to it is) selected. How do in invert my selection so that
instade 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.