Advanced Filter - Get the unique rows

[Kurt Biesemans]

Hello,

I have a worksheet (500 rows) with columns A till E
The values in column E look like this:

Antwerpen
Brussels
Antwerpen
Antwerpen
Mechelen
Antwerpen
Mechelen
Gent

Now with an advance filter I want to get the unique values in column E.
This filter looks like:
lastRow = MyWorksheet.range("A" & rows.count).end(xlup).row
MyWorksheet.Range("E2:E" & lastrow +1).Advancedfilter
Action:xlFilterInplace, Unique:=true

This give me a perfect list of the unique values in column E.
NOW!!! I need to loop over the unique values and put the values somewhere in
another worksheet.

Anybody any idea how I can select the rows and loop over them?

Regards
Kurt

 

[Dave Peterson]

If you just need to just move the unique list, you could have the advanced
filter do the work for you.

Dim LastRow As Long
Dim DestCell As Range
Dim myWorkSheet As Worksheet

Set myWorkSheet = Worksheets("sheet2")

Set DestCell = Worksheets.Add.Range("a1") 'A1 of a new worksheet.

With myWorkSheet
LastRow = .Range("A" & .Rows.Count).End(xlUp).Row
myWorkSheet.Range("E2:E" & LastRow).AdvancedFilter _
Action:=xlFilterCopy, copytorange:=DestCell, unique:=True
End With

My data has a header in E2 and I removed the +1 to the lastrow. I didn't
understand why you had it there.

You can change the destcell to any cell in any worksheet you want.

If you don't want the header, then delete it (shift up) after you've done the
advanced filter.

 

[ShaneDevenshire]

Hi,

When Excel copies from a filtered set of data it only picks up the visible
cells, so if you want to copy the whole list to a specific location, you can
do somenthing like this

Range("E1", [E1].End(xlDown)).Copy
Sheets("Sheet2").Range("A1").PasteSpecial