Selecting from a column and more...

[stenlake1]

I have a list of values, I would like my VBA script to scroll through these values until it finds a different value from the last.


Column A

256 (So want it to find this value)
256 (Then ignore this as previous to the last)

584 (Then find this value)
584
584
584

Is it best to use the "Selection.Find" command? Each time it finds a new value I would like it to prompt me so I can make a choice (going to insert another script in!)

Thanks for your help in advance :)

[Bob Phillips]

Sounds like a loop is best to me.

[mdmackillop]

Sub Macro1()
Dim tgt As Range
Dim Arr, a, c As Range
Set tgt = Range("K1")

'Create unique list
Columns("A:A").AdvancedFilter Action:=xlFilterCopy, CopyToRange:=tgt, Unique:=True
'Create an array of unique values
If tgt = tgt.Offset(1) Then
Arr = Range(tgt.Offset(1), Cells(Rows.Count, tgt.Column).End(xlUp)).Value
Else
Arr = Range(tgt, Cells(Rows.Count, tgt.Column).End(xlUp)).Value
End If
'Clear the filtered list
Range(tgt, Cells(Rows.Count, tgt.Column).End(xlUp)).ClearContents
'Find the first of each array member, ignoring blanks
For Each a In Arr
If a <> "" Then
Set c = Columns(1).Find(a, after:=Range("A" & Cells.Rows.Count))
'Do something
MsgBox c.Address
c.Interior.ColorIndex = 6
End If
Next
End Sub