This is about selecting multiple ranges in Excel using ctrl.

Say you click on A1, hold ctrl and click on A2, next A3, you have 3
cells selected. But if you accidentally click a second time on e.g.
A2, Selection.Cells.Count returns 4 and if you loop through the
elements within the Cells-collection, A2 is twice in it.
Is there a way to obtain all discrete cells from a range? I tried with
SpecialCells(xlCellTypeVisible) but no success.

tia,
Chris

Thanks a lot, Dave. I already found a solution looping through all
cells from the selection and storing unique cell addresses in an
array. But your (last) approach is a lot faster so I implemented it.
Great!
Chris

Dave Peterson wrote in message ...
Under light testing:
MsgBox Intersect(Selection, Selection).Cells.Count & " : in(sel,sel)"
worked ok

I ran this macro after selecting a bunch of overlapping multicolumn and multirow
areas in one selection and broke it real good.

MsgBox Selection.Cells.Count & " : selection"
MsgBox Intersect(Selection, Selection).Cells.Count & " : in(sel,sel)"
MsgBox Intersect(Rows, Selection).Cells.Count & " : in(rows,sel)"
MsgBox Intersect(Rows, Columns, Selection).Cells.Count & " :
in(rows,cols,sel)"


This might be slower, but I think I'd trust it to get the count:

Dim myRng As Range
Dim myCell As Range
For Each myCell In Selection.Cells
If myRng Is Nothing Then
Set myRng = myCell
Else
If Intersect(myRng, myCell) Is Nothing Then
Set myRng = Union(myRng, myCell)
Else
'do nothing, already there
End If
End If
Next myCell

MsgBox myRng.Cells.Count & " : MyRng"



chris wrote:

This is about selecting multiple ranges in Excel using ctrl.

Say you click on A1, hold ctrl and click on A2, next A3, you have 3
cells selected. But if you accidentally click a second time on e.g.
A2, Selection.Cells.Count returns 4 and if you loop through the
elements within the Cells-collection, A2 is twice in it.
Is there a way to obtain all discrete cells from a range? I tried with
SpecialCells(xlCellTypeVisible) but no success.

tia,
Chris