I've seen some threads about this but i can't get the code work.
This is the best code I've found for my situation:
- Private Sub CommandButton4_Click()
- Dim sCriteria As String, rngList As Range, sRngAddress As String
- sCriteria = Me.cboItem2.Value
- With ActiveSheet
- .AutoFilterMode = False
- .Range("A1:H" & .Cells(Rows.Count, "A").End(xlUp).Row).AutoFilter field:=5, Criteria1:=sCriteria
- Set rngList = .Range("A2:H" & .Cells(Rows.Count, "A").End(xlUp).Row).SpecialCells(xlCellTypeVisible)
- ' Sheet3.Cells.ClearContents
- ' rngList.Copy Sheet3.Range("A2")
- ' sRngAddress = "SHeet3!" & .Range("A2").SpecialCells(xlCellTypeVisible)
- End With
- ' With Me.ListBox1
- ' .RowSource = sRngAddress
- ' End With
- ' Set rngList = Nothing
- Me.ListBox1.List = rngList.Value
but I dont want to copy and paste, that's why I comment that lines. Just filter and show in the listbox, all in same sheet.
Actually, with the above code I see in the listbox only the first filtered row.
EDIT: Problem of only showing 1st row might be because when you have a filter, the range is divided in area and only the first one is passed?
I also have seen some comments about transposing the range to create a vector and pass it to the listbox?