Strange or error : For each cl in Selection 'cl as range

[Jean-Yves]

Hi All,

I found the following really strange :
if you make a multiple selection with the mouse while holding down <CTRL>,
and ,
if you Re-select a previously selected cell, this will also re Add this
cell to the selected range.

Result : <For each cl in selection> gives me the same range more than once
!!!
In the immediate window :
? selection.address
$CV$14,$CV$14,$CV$14,$CV$14,$CV$14,$CV$14,$CV$14,$CV$14

Question : is this normal, and if yes, who can I filter this list-array to
avoid duplicated entries.

Regards,

Jean-Yves

 

[Bernie Deitrick]

Jean-Yves,

Yes, it is normal.

Try the sub below to see how to work around it. But first, select your cells
multiple times....

HTH,
Bernie
MS Excel MVP

Sub CleanUpSelection()
Dim myRange As Range
Dim myCell As Range

MsgBox Selection.Address(False, False)

Set myRange = Selection(1)
For Each myCell In Selection
If Intersect(myCell, myRange) Is Nothing Then
If myRange Is Nothing Then
Set myRange = myCell
Else
Set myRange = Union(myRange, myCell)
End If
End If
Next myCell

MsgBox myRange.Address(False, False)

myRange.Select

End Sub

 

[Bernie Deitrick]

Forgot to clean up my code when I changed something:

Sub CleanUpSelection()
Dim myRange As Range
Dim myCell As Range

MsgBox Selection.Address(False, False)

Set myRange = Selection(1)
For Each myCell In Selection
If Intersect(myCell, myRange) Is Nothing Then
Set myRange = Union(myRange, myCell)
End If
Next myCell

MsgBox myRange.Address(False, False)

myRange.Select

End Sub


HTH,
Bernie
MS Excel MVP

 

[JY]

Hi Bernie,
Now I am home and I don't have XL on this pc.
Thank for your post. I reposted home (Subj. :Filter range array) and
Tom O also gave Intersect(selection,selection) to get unique cells.
Strange as well but it gives a result.
(A,B,A)intersect(A,B,A)=(A,B)

This is against my math logic.
I will contine on this tomorrow (Central european Time)
Regards
JY