You should add:

Option Explicit
to the top of your module.

You have some variables that use Rng and/or Range.
Dim myFilterRng As Range
Set myFilterRange = ....

The "option explicit" will catch those typos for you and could save you lots of
time later.

And to add to Jim's post, you'd want to do something like this:

listbox1.rowsource = myvisiblerange.address(external:=true)

but this won't work if your range is discontiguous. I think you'll have to loop
through those visible cells and .additem each value.

Option Explicit
Private Sub UserForm_Initialize()
Dim myVisibleRng As Range
Dim myFilterRng As Range
Dim myCell As Range
Dim cCtr As Long

Set myFilterRng = Sheet1.Range("a1:e13000")
'instead of 13000 rows, can you use a column (like A) to determine
'the lastrow?
'with sheet1
' set myfilterrng _
= .range("a1:e" & .cells(.rows.count,"A").end(xlup).row)
'end with

myFilterRng.AutoFilter Field:=5, Criteria1:="18650"

If myFilterRng.Columns(1) _
.SpecialCells(xlCellTypeVisible).Cells.Count = 1 Then
'no details found
Exit Sub
End If

With myFilterRng
Set myVisibleRng = .Resize(.Rows.Count - 1, 1) _
.Offset(1, 0).Cells _
.SpecialCells(xlCellTypeVisible)
End With

Debug.Print myVisibleRng.Address
With ListBox1
.ColumnCount = 5
For Each myCell In myVisibleRng.Cells
.AddItem myCell.Value
For cCtr = 2 To 5 'B:E
.List(.ListCount - 1, cCtr - 1) _
= myCell.Offset(0, cCtr - 1).Value
Next cCtr
Next myCell
End With
End Sub





hgdev wrote:

Excel 2000

I am trying to get AutoFilter data into a ListBox.
The below code gives me an "Type mismatch" error when I try to add the
Listbox Rowsource line. I read about using _FilterDatabase but could
not get the right syntax.

Private Sub UserForm_Initialize()
Dim myVisibleRng As Range
Dim myFilterRng As Range
Set myFilterRange = Sheet1.Range("a1:e13000")
myFilterRange.AutoFilter Field:=5, Criteria1:="18650"
Set myVisibleRange = myFilterRange.SpecialCells(xlCellTypeVisible)
Debug.Print myVisibleRange.Address
ListBox1.RowSource = myVisibleRange '<--type mismatch
End Sub

Thanks.

--

Dave Peterson