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

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" <(E-Mail Removed)> wrote in message
news:8EEDEAFF-68AA-48D5-8265-(E-Mail Removed)...
> 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(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