Hi,
The advanced Filter needs a heading in the 1st row selected, unfortunately by referring to a column in a table (listobj) only the data is passed to the range object in vba, a workaround:
With Range("Categories[Non-Core Comp. Map]")
.Offset(-1).Resize(.Rows.Count + 1).AdvancedFilter xlFilterCopy, , Range("C1"), True
End With
Note that you cannot use the adjacent column as target range, this will result in a application defined error as you cannot have 2 identical headings in a table.
dictionary solution:
Sub testDic()
Dim dic As Object: Set dic = CreateObject("Scripting.Dictionary")
Dim aData, i As Long, dKey
aData = Range("Categories[Non-Core Comp. Map]").Value 'sheet to array
For i = LBound(aData, 1) To UBound(aData, 1)
If Not IsEmpty(aData(i, 1)) Then
dic(aData(i, 1)) = 0 'array to dic (unique only)
End If
Next i
ReDim aData(1 To dic.Count, 1 To 1) 'prepare output array
i = 1
For Each dKey In dic.keys
aData(i, 1) = dKey 'dic to array
i = i + 1
Next dKey
Range("C2").Resize(UBound(aData, 1)).Value = aData 'array to sheet
Range("C1").Value = Range("Categories[Non-Core Comp. Map]").Offset(-1).Value 'heading
End Sub
Filter vs dictionary:
- When sheet is protected you cannot use filter
- Formatting is kept using filter, dic provides values only
- dic is 3 times faster (based on processing your test data 1000 times)
Bookmarks