Dave, gracious thanks.
Sorry, I neglected to say in my original post that I want the Headers
to appear in the Listbox. This was my reason for trying a different
approach such as the AutoFilter _FilterDatabase approach. Because I
knew(from testing) that .AddItem will not provide the natural Headings
that Rowsource produces.

Is there any line in your provided code that will allow the Headings?

Thanks

Dave Peterson wrote in message ...
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.