none
Autofilter results to listbox RRS feed

  • Question

  • I can't seem to figure out why my listbox results are not getting filtered as expected. I am getting all column results to listbox.

    please advise, thank you

    Private Sub CommandButton2_Click() 'testLB
    ListBox1.Clear
    Application.ScreenUpdating = True
    
    Dim LastRow As Long
    Dim index As Integer
    Dim Source As Range
    
    With ThisWorkbook
        Set Source = Worksheets("Sheet1").Range("$A$2:$AJ$1113")
        Source.AutoFilter Field:=2, Criteria1:=.Worksheets("Sheet2").Range("E9").Value
    End With
    
    For index = 1 To 1113
       ListBox1.AddItem (Source.Cells(index, 32))
    Next
    
    End Sub

    Saturday, May 14, 2016 3:25 PM

Answers

  • The loop

    For index = 1 To 1113
       ListBox1.AddItem (Source.Cells(index, 32))
    Next

    simply adds all values from column AF, whether filtered or not. Try this version:

    Private Sub CommandButton2_Click() 'testLB
        Dim varValue As Variant
        Dim cel As Range
        ListBox1.Clear
        varValue = Worksheets("Sheet2").Range("E9").Value
        For Each cel In Worksheets("Sheet1").Range("B2:B113")
            If cel.Value = varValue Then
                ListBox1.AddItem cel.Offset(0, 30).Value
            End If
        Next cel
    End Sub


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Saturday, May 14, 2016 4:05 PM

All replies

  • The loop

    For index = 1 To 1113
       ListBox1.AddItem (Source.Cells(index, 32))
    Next

    simply adds all values from column AF, whether filtered or not. Try this version:

    Private Sub CommandButton2_Click() 'testLB
        Dim varValue As Variant
        Dim cel As Range
        ListBox1.Clear
        varValue = Worksheets("Sheet2").Range("E9").Value
        For Each cel In Worksheets("Sheet1").Range("B2:B113")
            If cel.Value = varValue Then
                ListBox1.AddItem cel.Offset(0, 30).Value
            End If
        Next cel
    End Sub


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Saturday, May 14, 2016 4:05 PM
  • Thanks Hans!I ended up having to Trim, my sheet2 variable, but without a doubt your code got it done.
    Saturday, May 14, 2016 10:07 PM
  • If this solved your problem, then please mark it as an answer and to give kudos to Hans

    Best regards, George

    Sunday, May 15, 2016 11:41 AM