Filter Rowsource for Listbox

[Noah]

I have the following range set as the rowsource in my listbox:

Dim rng As Range
lastrow = Cells(1, 1).End(xlDown).Row
Set rng = Sheet1.Range(Sheet1.Cells(1, 1), Sheet1.Cells(lastrow, 2))
Me.ListBox1.RowSource = rng.Address

Cells in column 3 have either a "Yes" or a "No" value inside them. Is there
a way for me to filter the range ("rng") so that the listbox only shows rows
with a "yes" in column 3? I tried running an autofilter, but the listbox
still included all cells in rng.

In addition, is it possible to show two non-adjacent columns in a listbox
(ie column A and column C, but not column B)? If so, how?

Thanks!

 

[chijanzen]

Noah:

try use array

Private Sub CommandButton1_Click()
Dim rng As Range, r As Range
Columns("C:C").AutoFilter Field:=1, Criteria1:="yes"
lastrow = Cells(1, 1).End(xlDown).Row
Set rng = Sheet1.Range(Sheet1.Cells(1, 1), Sheet1.Cells(lastrow, 1))
Set rng = rng.SpecialCells(xlCellTypeVisible)
ReDim rTab(0 To rng.Count - 1, 1 To 2)
i = 0
For Each r In rng
rTab(i, 1) = r.Value
rTab(i, 2) = r.Offset(, 1)
i = i + 1
Next
Me.ListBox1.List = rTab
End Sub

 

[chijanzen]

Noah:

if yuor data in Worksheets("Sheet1")

try,

Private Sub CommandButton1_Click()
Dim rng As Range, r As Range
With Worksheets("Sheet1")
lastrow = .Range("R65536").End(xlUp).Row
.Range("R6:R" & lastrow).AutoFilter Field:=1, Criteria1:="Yes"
Set rng = .Range(.Cells(7, 1), .Cells(lastrow, 1))
Set rng = rng.SpecialCells(xlCellTypeVisible)
ReDim rTab(0 To rng.Count - 1, 1 To 2)
i = 0
For Each r In rng
rTab(i, 1) = r.Offset(, 1)
rTab(i, 2) = r.Offset(, 18)
i = i + 1
Next
End With
With UserForm1
.ListBox1.ColumnCount = 2
.ListBox1.ColumnWidths = "110,20"
.ListBox1.List = rTab
End With
End Sub

The column heads only use rowsource or listfill range
property

 

[Noah]

Thank you once again for your help. Your method seems to work pretty well,
but now I have another question. With the method that you devised for
filtering the list in the listbox, is it possible to also use the
listbox1.listindex property? In other words, if the user selects an item in
the filtered list, is it possible to find out what row the item corresponds
to in the unfiltered worksheet? Thank you!