Populate ListBox with Filtered Range

**smartbuyer** · 07-20-2015, 11:56 PM

Hi,

I would like to fill my listbox with the date of the filtered sheet (column A)

here's what I'm using currently

Private Sub UserForm_Initialize()
TextBox1.Text = Sheets("Call Stats").Range("G3").Value

Dim i As Long, j As Long
  lastrow = 1
  For i = 1 To 3
      j = Sheets("Notes").Cells(Rows.Count, i).End(xlUp).Offset(1, 0).Row
      If lastrow < j Then lastrow = j
Next i

Sheets("Notes").Activate
ListBox1.RowSource = Range("A2" & lastrow & ":" & "C" & lastrow).Address
vntList = Sheets("Notes").Range("A2" & lastrow & ":" & "C" & lastrow)

ListBox1.ColumnWidths = "60;70;200"
TextBox2.Value = lastrow - 2
Sheets("Call Stats").Activate

End Sub

the problem I am having is that
1. the header is showing the first data row not my headers
2. when the filters change, it doesnt add just the filtered

here's a sample file: Book1.xlsmitems

**humdingaling** · 07-21-2015, 01:09 AM

dont think you can use rowsource for what you are doing
also i found your listbox1 was way too small to show the data you were trying to get?

see attached possible solution
using loop for Rng of special cells visible then loop for Listbox add item
i tried to leave as much of your original code as possible without deleting it

Dim lastrow As Long
Public bDontRun As Boolean

Private Sub UserForm_Initialize()
    'TextBox1.Text = Sheets("Call Stats").Range("G3").Value
    Dim rng As Range
    Dim rw As Range
    
    Dim i As Long, j As Long
    
    lastrow = Cells(Rows.Count, "A").End(xlUp).Row
    Set rng = Range("A2:A" & lastrow).SpecialCells(xlCellTypeVisible)
    
    Sheets("Notes").Activate
    
    For Each rw In rng
        ListBox1.AddItem
        For i = 1 To 3
            ListBox1.List(ListBox1.ListCount - 1, i - 1) = rw.Cells(1, i).Value
        Next i
    Next rw
    
    
    'vntList = Sheets("Notes").Range("A2" & lastrow & ":" & "C" & lastrow)
    
    ListBox1.ColumnWidths = "60;70;200"
    TextBox2.Value = rng.Count
    'Sheets("Call Stats").Activate

End Sub