Filter range array

[JY]

Hi all,

My previous post does get any answer (maybe to strange)

I have different sub that interfere with all selected cells on the active
worksheet.
If a non contiguous range of cells was selected using CTRL + mouse,
and, if accidentally, the same cell was clicked more than once, the
resulting range will
contain more than once the same cell address.
Hold down CTRL and click 3 time A1 :
in the immediate window -
? selection.address
$A$1,$A$1,,$A$1

Sub Test
dim cl as range
For each cl in selection
Debug.print cl.address
next cl
end sub
Result : 3 iterations

Question : how can I get the unique record(s) in that range and not get an
entry each time the user click the same cell - I known
, you click only once but still ?

Regards,
..
Jean-Yves

 

[Tom Ogilvy]

Use Intersection to clean it up:

from the immediate window:

? selection.Address
$B$2,$C$2,$B$4,$C$2,$C$4,$B$2,$B$4
? Intersect(selection,selection).Address
$B$2:$C$2,$B$4:$C$4
intersect(selection,Selection).Select
? selection.Address
$B$2:$C$2,$B$4:$C$4

 

[JY]

Thank you for the solution,

But why can the same cell be selected more than once ??!!

Regards
Jean-Yves

 

[Tom Ogilvy]

You will have to talk to the designers of excel.