Selecting items

[Steven]

When you select items by holding down the Ctrl key and clicking a cell or
cells in order to get the sum of a group of items .... how do you unselect a
specific cell if you accidentally selected it in error?

Thank you,

Steven

 

[T. Valko]

AFAIK, you'd have to start over. Or, make a mental note of the value of the
mistakenly selected cell then subtract that from the grand total.

 

[Rick Rothstein]

You also have to make sure you don't accidentally select an already selected
item... if you do, it will be included in the selection twice (or more if
you select it again multiple times)! Here is a demonstration for those not
familiar with this "quirk". Leaving the cells empty, make a multi-cell
selection by Ctrl+Clicking on each cell in the following order...

A1 A5 A10 A5 A20

After you do that, A5 will be included in the selection twice. To see this,
copy/paste and then run this macro...

Sub Test()
Dim Cell As Range
For Each Cell In Selection
Cell = Cell + 2
Next
End Sub

Since the cells are empty when you start, you would expect each cell to now
contain the value 2, but take a look at what is in A5. You can also see the
double inclusion of A5 in the selection by executing this line in the
Immediate Window...

? Selection.Address

However, I think the above macro better demonstrates how erroneous outputs
can possibly occur by iterating the Selection range.

 

[Gord Dibben]

Once selected with CTRL + Click, the cells are selected.

Chip Pearson has code to allow de-selecting cells or ranges.

Sub UnSelectActiveCell()
Dim Rng As Range
Dim FullRange As Range
'Chip Pearson
If Selection.Cells.Count > 1 Then
For Each Rng In Selection.Cells
If Rng.Address <> ActiveCell.Address Then
If FullRange Is Nothing Then
Set FullRange = Rng
Else
Set FullRange = Application.Union(FullRange, Rng)
End If
End If
Next Rng

If FullRange.Cells.Count > 0 Then
FullRange.Select
End If
End If

End Sub


Sub UnSelectActiveArea()
'Chip Pearson
Dim Rng As Range
Dim FullRange As Range
Dim Ndx As Integer
If Selection.Areas.Count > 1 Then
For Each Rng In Selection.Areas
If Application.Intersect(ActiveCell, Rng) Is Nothing Then
If FullRange Is Nothing Then
Set FullRange = Rng
Else
Set FullRange = Application.Union(FullRange, Rng)
End If
End If
Next Rng
FullRange.Select
End If

End Sub


Gord Dibben MS Excel MVP