I have a table in excel. When I filter it I get anumber of rows as my filter
result. Now how do I transfer this filter result into a list box. I know I
have to use something like:

ListBox1.RowSource = "a2:e13"

to populate the listbox, but the resulting rows from my filter are not
consecutive so I can exactly use this. What I need to know is any other way
to get the filter result into the listbox.
Any direction will be greatly appreciated.
Thanks
Ayo

Verrrrryyyy close!
Set the reference in the Properties. View Properties Window
RowSource=Sheet1!A1:A9

Then...

If you want to send the data from the Form back to the sheet use this:
Sheets("Sheet1").Activate
Cells(1, 2) = ComboBox1.Text
'etc., etc., etc.

Drop the quotes.

HTH,
Ryan---

"Ayo" wrote:

I have a table in excel. When I filter it I get anumber of rows as my filter
result. Now how do I transfer this filter result into a list box. I know I
have to use something like:

ListBox1.RowSource = "a2:e13"

to populate the listbox, but the resulting rows from my filter are not
consecutive so I can exactly use this. What I need to know is any other way
to get the filter result into the listbox.
Any direction will be greatly appreciated.
Thanks
Ayo

You can't use .rowsource if the range is discontiguous--and I would expect that
your filtered data would be discontiguous at least some of the time.

Option Explicit
Private Sub CommandButton1_Click()
Unload Me
End Sub
Private Sub UserForm_Initialize()

Dim wks As Worksheet
Dim VisRng As Range
Dim myCell As Range
Dim iCtr As Long
Dim HowManyCols As Long

Set wks = Worksheets("sheet1")

With wks
With .AutoFilter.Range
HowManyCols = .Columns.Count
If .Columns(1).Cells.SpecialCells(xlCellTypeVisible). Count = 1 Then
'nothing visible
Set VisRng = Nothing
Else
'resize to avoid the header
'and come down one row
Set VisRng = .Resize(.Rows.Count - 1, 1).Offset(1, 0) _
.Cells.SpecialCells(xlCellTypeVisible)
End If
End With
End With

With Me.ListBox1
.ColumnCount = HowManyCols
.MultiSelect = fmMultiSelectMulti '???
.RowSource = ""
End With

If VisRng.Cells.Count = wks.AutoFilter.Range.Rows.Count - 1 Then
'use the .rowsource property
Me.ListBox1.RowSource _
= VisRng.Resize(, HowManyCols).Address(external:=True)
Else
'loop through the visible cells in the first column.
With Me.ListBox1
For Each myCell In VisRng.Cells
.AddItem myCell.Value
For iCtr = 1 To HowManyCols - 1
.List(.ListCount - 1, iCtr) = myCell.Offset(0, iCtr).Value
Next iCtr
Next myCell
End With
End If

End Sub

Ayo wrote:

I have a table in excel. When I filter it I get anumber of rows as my filter
result. Now how do I transfer this filter result into a list box. I know I
have to use something like:

ListBox1.RowSource = "a2:e13"

to populate the listbox, but the resulting rows from my filter are not
consecutive so I can exactly use this. What I need to know is any other way
to get the filter result into the listbox.
Any direction will be greatly appreciated.
Thanks
Ayo

--

Dave Peterson

There's a bug in that code if the visible range only consists of the headers:

Option Explicit
Private Sub CommandButton1_Click()
Unload Me
End Sub
Private Sub UserForm_Initialize()

Dim wks As Worksheet
Dim VisRng As Range
Dim myCell As Range
Dim iCtr As Long
Dim HowManyCols As Long

Set wks = Worksheets("sheet1")

With wks
With .AutoFilter.Range
HowManyCols = .Columns.Count
If .Columns(1).Cells.SpecialCells(xlCellTypeVisible). Count = 1 Then
'nothing visible
Set VisRng = Nothing
Else
'resize to avoid the header
'and come down one row
Set VisRng = .Resize(.Rows.Count - 1, 1).Offset(1, 0) _
.Cells.SpecialCells(xlCellTypeVisible)
End If
End With
End With

With Me.ListBox1
.ColumnCount = HowManyCols
.MultiSelect = fmMultiSelectMulti '???
.RowSource = ""
End With

'added this check
If VisRng Is Nothing Then
'nothing showing, what should happen
Else
If VisRng.Cells.Count = wks.AutoFilter.Range.Rows.Count - 1 Then
'use the .rowsource property
Me.ListBox1.RowSource _
= VisRng.Resize(, HowManyCols).Address(external:=True)
Else
'loop through the visible cells in the first column.
With Me.ListBox1
For Each myCell In VisRng.Cells
.AddItem myCell.Value
For iCtr = 1 To HowManyCols - 1
.List(.ListCount - 1, iCtr) _
= myCell.Offset(0, iCtr).Value
Next iCtr
Next myCell
End With
End If
End If

End Sub

Dave Peterson wrote:

You can't use .rowsource if the range is discontiguous--and I would expect that
your filtered data would be discontiguous at least some of the time.

<<snipped
--

Dave Peterson

--

Dave Peterson

Thanks Dave. I will try this.

"Dave Peterson" wrote:

There's a bug in that code if the visible range only consists of the headers:

Option Explicit
Private Sub CommandButton1_Click()
Unload Me
End Sub
Private Sub UserForm_Initialize()

Dim wks As Worksheet
Dim VisRng As Range
Dim myCell As Range
Dim iCtr As Long
Dim HowManyCols As Long

Set wks = Worksheets("sheet1")

With wks
With .AutoFilter.Range
HowManyCols = .Columns.Count
If .Columns(1).Cells.SpecialCells(xlCellTypeVisible). Count = 1 Then
'nothing visible
Set VisRng = Nothing
Else
'resize to avoid the header
'and come down one row
Set VisRng = .Resize(.Rows.Count - 1, 1).Offset(1, 0) _
.Cells.SpecialCells(xlCellTypeVisible)
End If
End With
End With

With Me.ListBox1
.ColumnCount = HowManyCols
.MultiSelect = fmMultiSelectMulti '???
.RowSource = ""
End With

'added this check
If VisRng Is Nothing Then
'nothing showing, what should happen
Else
If VisRng.Cells.Count = wks.AutoFilter.Range.Rows.Count - 1 Then
'use the .rowsource property
Me.ListBox1.RowSource _
= VisRng.Resize(, HowManyCols).Address(external:=True)
Else
'loop through the visible cells in the first column.
With Me.ListBox1
For Each myCell In VisRng.Cells
.AddItem myCell.Value
For iCtr = 1 To HowManyCols - 1
.List(.ListCount - 1, iCtr) _
= myCell.Offset(0, iCtr).Value
Next iCtr
Next myCell
End With
End If
End If

End Sub

Dave Peterson wrote:

You can't use .rowsource if the range is discontiguous--and I would expect that
your filtered data would be discontiguous at least some of the time.

<<snipped
--

Dave Peterson

--

Dave Peterson