How do I insert a Filter range result into a ListBox

[Ayo]

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

 

[RyGuy]

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---

 

[Dave Peterson]

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

 

[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

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.

 

[Ayo]

Thanks Dave. I will try this.

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

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.