Fill 2 column list box with Advanced Data Filter results

[John]

I would like to fill a 2 column ListBox with unique items obtained from
Advanced Data Filter. Here is the code that puts my Advanced Data Filter
results on the worksheet:

Range("MyRange").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Range _
("CritRange"), CopyToRange:=Range("ExtractRange"), Unique:=True


I would like those results to be displayed directly in a 2 column ListBox,
without having to display them first on the worksheet. Thank you.

 

[john]

just filter the data to a hidden sheet.

something like following may do what you want. Adapt as required.

Sub FilterData()
Dim ws1 As Worksheet
Dim ws2 As Worksheet
Dim rng As Range

Set ws1 = Worksheets("Sheet1")

Set ws2 = Worksheets("Sheet2")

ws1.Range("MyRange").AdvancedFilter Action:=xlFilterCopy, _
CriteriaRange:= _
ws1.Range("CritRange"), _
CopyToRange:= _
ws2.Range("A1"), _
Unique:=True

With ws2

Set rng = .Range(.Cells(2, 1), .Cells(.Rows.Count, 2).End(xlUp))

End With

With UserForm1

With .ListBox1
.ColumnCount = 2
.RowSource = rng.Address
.ColumnHeads = True
.ColumnWidths = "100;100"

End With

.Show

End With

rng.ClearContents

End Sub