asked on Excel - Filter Copy/Paste Unique Items
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:
How can this best be reliably accomplished so that only the unique values from the Filtered Visible Rows are copy/pasted?
Cook09
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
How can this best be reliably accomplished so that only the unique values from the Filtered Visible Rows are copy/pasted?
Cook09
Microsoft ExcelMicrosoft Office
Last Comment
Cook09
ASKER
Norie,
Possibly...I have Columns A-H which will be filtered...I can then Copy those visible rows to L-S...and then copy/paste the Unique values to Column X. Is that what your referring too? How would that code sequence work?
Cook09
Possibly...I have Columns A-H which will be filtered...I can then Copy those visible rows to L-S...and then copy/paste the Unique values to Column X. Is that what your referring too? How would that code sequence work?
Cook09
ASKER
You pointed me in the right direction...just did multiple filters, and copied to static range and extracted unique from that one.
What I noticed though is to fully clear a double filter, the "ShowAllData" needs to be run twice. Is that normal?
Another quick question: Since the Extracted Unique Data was going into a combobox, how does one have the first value show up in the combobox as the first selection, instead of seeing a blank and then having to select the down arrow to view the different options.
What I noticed though is to fully clear a double filter, the "ShowAllData" needs to be run twice. Is that normal?
Another quick question: Since the Extracted Unique Data was going into a combobox, how does one have the first value show up in the combobox as the first selection, instead of seeing a blank and then having to select the down arrow to view the different options.
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
Thanks for the guidance..
Cook09
Cook09
What you could do, and I know it's not ideal, is copy the result of the advanced filter to another (temporary) sheet and then do the advanced filter on that sheet returning the results to the original sheet.
Would that be of any use?