Dim an array from a filtered Range

[CBartman]

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(xlCellTypeVisible).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).Rows.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

 

[Bernie Deitrick]

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