Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Filter Rowsource for Listbox
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! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Filter Rowsource for Listbox
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(xlCellTypeVisib le) 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! |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Filter Rowsource for Listbox
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(xlCellTypeVisib le) 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! |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Filter Rowsource for Listbox
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(xlCellTypeVisib le) 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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
RowSource in ListBox | Excel Programming | |||
Listbox.RowSource problem | Excel Programming | |||
RowSource in ListBox | Excel Programming | |||
Is refreshing listbox rowsource in listbox click event possible? | Excel Programming | |||
listbox rowsource | Excel Programming |