Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Dim an array from a filtered Range
Would ultimately like to use array for userform combobox source.
Excel 2003 I seem to be having trouble with "non-consecutive" rows. "Banging" my head. Any help would be appreciated. Thanks Sub RangeFilter() Dim w As Worksheet Dim filterArray() Dim currentFiltRange As String Set w = Workbooks("My-Stuff.xls").Worksheets("My-Machines") With w.ListObjects(1) 'filter the list (by column 5 data) based on userform textbox.text currentFiltRange = .Range.AutoFilter(5, tbAcctNum.Text) 'sheet data is filtered correctly MsgBox w.ListObjects(1).DataBodyRange.SpecialCells(xlCell TypeVisible).Address 'Example result (based on selection)- $A$2:$P$4, $A$14:$P$32, $A$36:$P$38 'visible results of filter. These addresses are correct. ReDim filterArray(0 To f) 'An attempt to include only visible rows in an array For f = 0 To .DataBodyRange.SpecialCells(xlCellTypeVisible).Row s.Count 'code to include ALL visible rows. Next f MsgBox f 'showing me number of visible rows for the above example as: 10 'incorrectly reporting number of visible rows (actual number of rows: 13) End With End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Dim an array from a filtered Range
C,
If you sort your table and then filter it, you can use the filtered values from the range directly as the rowsource of the combobox. I have assumed that you have a header row on your list. Private Sub UserForm_Initialize() Dim myR As Range Dim myStr As String Set myR = Workbooks("My-Stuff.xls").Worksheets("My-Machines").ListObjects(1).Range myR.Sort Key1:=myR.Cells(1, 5), Order1:=xlAscending, Header:=xlYes, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal myR.AutoFilter Field:=5, Criteria1:=tbAcctNum.Text Set myR = myR.Offset(1, 0).Resize(myR.rows.Count - 1) _ .SpecialCells(xlCellTypeVisible) myStr = myR.Parent.Name & "!" & myR.Address Me.ComboBox1.RowSource = myStr myR.AutoFilter End Sub HTH, Bernie MS Excel MVP "CBartman" wrote in message ... Would ultimately like to use array for userform combobox source. Excel 2003 I seem to be having trouble with "non-consecutive" rows. "Banging" my head. Any help would be appreciated. Thanks Sub RangeFilter() Dim w As Worksheet Dim currentFiltRange As String Set w = Workbooks("My-Stuff.xls").Worksheets("My-Machines") With w.ListObjects(1) 'filter the list (by column 5 data) based on userform textbox.text currentFiltRange = .Range.AutoFilter(5, tbAcctNum.Text) 'sheet data is filtered correctly MsgBox w.ListObjects(1).DataBodyRange.SpecialCells(xlCell TypeVisible).Address 'Example result (based on selection)- $A$2:$P$4, $A$14:$P$32, $A$36:$P$38 'visible results of filter. These addresses are correct. ReDim filterArray(0 To f) 'An attempt to include only visible rows in an array For f = 0 To .DataBodyRange.SpecialCells(xlCellTypeVisible).Row s.Count 'code to include ALL visible rows. Next f MsgBox f 'showing me number of visible rows for the above example as: 10 'incorrectly reporting number of visible rows (actual number of rows: 13) End With End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Redimming an array dynamically assigned from range (how to redim first dimension of a 2-D array? /or/ reverse the original array order) | Excel Programming | |||
Array copying to a filtered region | Excel Programming | |||
Filtered Array for Listbox | Excel Programming | |||
Trying to count text in a filtered array using this function | Excel Programming | |||
traversing through a filtered range based on another filtered range | Excel Programming |