I'm struggling with the advanced filter using VBA.
In my case, the user selects an item from a ComboBox (e.g. "Apple"). Then, from a worksheet, I want to copy all unique "Brands" from Col G that match "Apple" (col B) to the last column in the sheet.
This is what I'm trying, which doesn't work. The problem I believe is with setting the CriteriaRange. Can anyone help out?
In my case, the user selects an item from a ComboBox (e.g. "Apple"). Then, from a worksheet, I want to copy all unique "Brands" from Col G that match "Apple" (col B) to the last column in the sheet.
This is what I'm trying, which doesn't work. The problem I believe is with setting the CriteriaRange. Can anyone help out?
Code:
With wsSheet
' Add the filter criteria
' column heading (e.g. Fruit)
.Range("IU1").Value = .Range("B1")
' item selected from a combobox (e.g. Apple)
.Range("IU2").Value = frmOptions.cboFrom.Value
' Define the range for the Advanced Filter (e.g. Brand).
Set rngData = .Range(.Range("G1"), .Range("G65536").End(xlUp))
' Create a unique list and copy to Last column.
rngData.AdvancedFilter Action:=xlFilterCopy, CriteriaRange:= _
.Range("IU1:IU2"), CopyToRange:=Range("IV1"), Unique:=True
' Read the filtered unique list into a variant array.
varData = .Range(.Range("IV2"), .Range("IV65536").End(xlUp)).Value
' Delete the temporarily created unique list.
.Range(.Range("IV1"), .Range("IV65536").End(xlUp)).ClearContents
.Range("IU1:IU2").ClearContents
End With