troubleshooting Question

Excel - Filter Copy/Paste Unique Items

Avatar of Cook09
Cook09Flag for United States of America asked on
Microsoft ExcelMicrosoft Office
5 Comments1 Solution181 ViewsLast Modified:
I'm trying to perform an AutoFilter on a given set of Data, and with those Visible Rows, copy/paste the unique items from one Column to a Second Column on the same page.

The code would appear to be straightforward:
Sub FilterCopy
Dim wsData As Worksheet, wsLane As Worksheet
Dim DataLR As Long

Set wsLane = Worksheets("LANES")
Set wsData = Worksheets("FMData")

DataLR = wsData.Range("A" & Rows.Count).End(xlUp).Row

wsData.Range("X2:X" & DataLR).ClearContents

   'AutoFilter...Result = Filtered Visible Rows
wsData.Range("A2:H" & DataLR).AutoFilter Field:=2, Criteria1:="=*" & wsLane.[E9] & "*"

   'Copy/Paste Unique Values
wsData.Range("E2:E" & DataLR).AdvancedFilter Action:=xlFilterCopy, CopyToRange:=wsData.Range("X2"), Unique:=True
End Sub
What occurs is the filtered table becomes unfiltered and all unique values from Column E are copied to Column X.  Far more than needed.

How can this best be reliably accomplished so that only the unique values from the Filtered Visible Rows are copy/pasted?

NorieSenior Associate
Join our community to see this answer!
Unlock 1 Answer and 5 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 5 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros