Let’s say that you’ve selected a large range of cells, such as A7:R182. You want to perform some sort of operation on all the cells in this range, except a few. You might wonder how to remove a couple of cells within the range from the selection set, so you hold down the Ctrl key as you click on those cells. That doesn’t work; Excel simply unselects the range you previously selected.
There is no way to change this behavior within Excel itself. Instead, you need to turn to other solutions. One is to use a macro, such as the following:
Sub UnSelectSomeCells()
Dim rSelect As Range
Dim rUnSelect As Range
Dim rNew As Range
Dim rCell As Range
Set rSelect = Selection
Set rUnSelect = Application.InputBox( _
"What cells do you want to exclude?", Type:=8)
For Each rCell In rSelect
If Intersect(rCell, rUnSelect) Is Nothing Then
If rNew Is Nothing Then
Set rNew = rCell
Else
Set rNew = Union(rNew, rCell)
End If
End If
Next
rNew.Select
Set rCell = Nothing
Set rSelect = Nothing
Set rUnSelect = Nothing
Set rNew = Nothing
End Sub
To use the macro, select the entire range you want to start with, such as A7:R182. Then run the macro. You are asked to choose the cells to be unselected. You can do so by simply selecting the cells with the mouse, holding down the Shift key as you click on each one. When you dismiss the input box, the selection you started with is modified to exclude the cells you selected.
If you prefer to not use your own macros, you can find help for deselecting cells in a selected range by using third-party tools, such as the ASAP Utilities. You can find their Excel tools at this Web page:
http://www.asap-utilities.com/asap-utilities-excel-tools.php
The tool applicable to this tip is the Select tool.
