How do I insert a Filter range result into a ListBox
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
|