Filter Rowsource for Listbox

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!

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



--
天行健，君�*�以自強不息
地勢坤，君�*�以厚德載物

http://www.vba.com.tw/plog/


"Noah" wrote:

> 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!

Thank you for your help. The autofilter seems to work with the listbox, but
I am having trouble adopting the code below to the specifics of my worksheet.
My worksheet contains data from A7 to U1000. Row 6 contains column
headings. I would like to filter the data by column R, and then just have
columns B and S in the listbox with my column headings of B6 and S6. Is this
possible? The code that I currently have is below. Thanks!

Sub Macro1()
Dim rng As Range, r As Range
Columns("R:R").AutoFilter Field:=18, Criteria1:="Yes"
lastrow = Sheet1.Cells(7, 2).End(xlDown).Row
Set rng = Sheet4.Range(Cells(7, 1), Cells(lastrow_2, 21))
Set rng = Standard_Issuer_Range.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
With userform1
.ListBox1.List = rTab
.ListBox1.ColumnCount = 2
.ListBox1.ColumnWidths = "110,20"
End With
End sub

"chijanzen" wrote:

> 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
>
>
>
> --
> 天行健，君�*�以自強不息
> 地勢坤，君�*�以厚德載物
>
> http://www.vba.com.tw/plog/
>
>
> "Noah" wrote:
>
> > 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!

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
--
天行健，君�*�以自強不息
地勢坤，君�*�以厚德載物

http://www.vba.com.tw/plog/


"Noah" wrote:

> Thank you for your help. The autofilter seems to work with the listbox, but
> I am having trouble adopting the code below to the specifics of my worksheet.
> My worksheet contains data from A7 to U1000. Row 6 contains column
> headings. I would like to filter the data by column R, and then just have
> columns B and S in the listbox with my column headings of B6 and S6. Is this
> possible? The code that I currently have is below. Thanks!
>
> Sub Macro1()
> Dim rng As Range, r As Range
> Columns("R:R").AutoFilter Field:=18, Criteria1:="Yes"
> lastrow = Sheet1.Cells(7, 2).End(xlDown).Row
> Set rng = Sheet4.Range(Cells(7, 1), Cells(lastrow_2, 21))
> Set rng = Standard_Issuer_Range.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
> With userform1
> .ListBox1.List = rTab
> .ListBox1.ColumnCount = 2
> .ListBox1.ColumnWidths = "110,20"
> End With
> End sub
>
> "chijanzen" wrote:
>
> > 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
> >
> >
> >
> > --
> > 天行健，君�*�以自強不息
> > 地勢坤，君�*�以厚德載物
> >
> > http://www.vba.com.tw/plog/
> >
> >
> > "Noah" wrote:
> >
> > > 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!

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!

"chijanzen" wrote:

> 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
> --
> 天行健，君�*�以自強不息
> 地勢坤，君�*�以厚德載物
>
> http://www.vba.com.tw/plog/
>
>
> "Noah" wrote:
>
> > Thank you for your help. The autofilter seems to work with the listbox, but
> > I am having trouble adopting the code below to the specifics of my worksheet.
> > My worksheet contains data from A7 to U1000. Row 6 contains column
> > headings. I would like to filter the data by column R, and then just have
> > columns B and S in the listbox with my column headings of B6 and S6. Is this
> > possible? The code that I currently have is below. Thanks!
> >
> > Sub Macro1()
> > Dim rng As Range, r As Range
> > Columns("R:R").AutoFilter Field:=18, Criteria1:="Yes"
> > lastrow = Sheet1.Cells(7, 2).End(xlDown).Row
> > Set rng = Sheet4.Range(Cells(7, 1), Cells(lastrow_2, 21))
> > Set rng = Standard_Issuer_Range.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
> > With userform1
> > .ListBox1.List = rTab
> > .ListBox1.ColumnCount = 2
> > .ListBox1.ColumnWidths = "110,20"
> > End With
> > End sub
> >
> > "chijanzen" wrote:
> >
> > > 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
> > >
> > >
> > >
> > > --
> > > 天行健，君�*�以自強不息
> > > 地勢坤，君�*�以厚德載物
> > >
> > > http://www.vba.com.tw/plog/
> > >
> > >
> > > "Noah" wrote:
> > >
> > > > 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!