Use VBA to get unique values

[cdhoughton]

Hi all

I have a number of values in the column E:E, e.g.

8882150
8882150
8882211
8882150
3022014
8882211
3022014
...

I want to use VBA to capture each value in the list once and ignore other duplicates and then assign the value to a variable, ie get the following

Emp1 = 8882150
Emp2 = 8882211
Emp3 = 3022014

I'm not sure how to do this efficiently without a big For loop and a big Or statement - can anyone advise how best to go about this?

Cheers

Chris

 

[jindon]

1) AdvancedFilter to get Unique Values (need header)
2) Place formula = "Emp"&Row() column next to it
3) convert formula to value

so no loop needed..

 

[cdhoughton]

Hi jindon

I'm afraid I've not used AdvancedFilter before - any tips on how to code this in practice?

Cheers

Chris

 

[jindon]

Something like this..

Sub Sample()
With Sheets("Sheet1") '<- change to suite
      .Range("e1",.Range("e" & Rows.Count).End(xlUp)).AdvancedFilter _
         Action:=xlFilterCopy, CopyToRange:=.Range("g1"),Unique:=True
      With .Range("g2",.Range("g" & Rows.Count).End(xlUp)).Offset(,-1)
             .Formula= "=""Emp""&Row()-1"
             .Value = .Value
      End With
End With
End Sub

 

[cdhoughton]

Cheers jindon!