Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Advanced Filter - Get the unique rows
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Advanced Filter - Get the unique rows
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. Kurt Biesemans wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Advanced Filter - Get the unique rows
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 -- Thanks, Shane Devenshire "Kurt Biesemans" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
unique records by advanced filter | Excel Discussion (Misc queries) | |||
Unique Filter Code / Advanced Filter | Excel Programming | |||
Advanced Filter Unique Records and Hidden Rows | Excel Programming | |||
Error using Advanced Filter Unique Records | Excel Discussion (Misc queries) | |||
Advanced Filter - Unique Values | Excel Programming |