Listbox selection from a filtered list (VBA)

Question

I'm new to VBA and would appreciate any guidance on a current problem. I'm trying to create a list box which is populated based on the user's previous selections. I have used the DoFilter based on two cells "A2" and "B2" to reduce the number of rows (which I want to populate ListBox1)

Sub DoFilter()

Dim rCrit1 As Range, rCrit2 As Range, rRng1 As Range, rRng2 As Range

With Application
    .EnableEvents = False
    .ScreenUpdating = False
End With

Set rCrit1 = Sheets("QuestionBank").Range("A2")
Set rCrit2 = Sheets("QuestionBank").Range("B2")

Set rRng1 = Sheets("QuestionBank").Range("A5:AA2300")
Set rRng2 = Sheets("QuestionBank").Range("A6:AA2300")

With rRng1
    .AutoFilter Field:=1, Criteria1:=rCrit1.Value, Operator:=xlOr
    .AutoFilter Field:=2, Criteria1:=rCrit2.Value

End With

    Application.EnableEvents = True
End Sub

I would then like this filtered range to populate my listbox1. However, as it currently stands it populates it with all the data, rather than just the filtered data.

Private Sub Worksheet_Activate()
    Dim myCell As Range
    Dim rngItems As Range
    Set rngItems = Sheets("QuestionBank").Range("ItemList") 'ItemList refers to a column of data which populates the ListBox    

Me.ListBox1.MultiSelect = fmMultiSelectSingle
Me.ListBox1.Value = ""
Me.ListBox1.MultiSelect = fmMultiSelectMulti
Me.ListBox1.Value = ""


    Me.ListBox1.Clear
    Me.ListBox2.Clear

    With Me.ListBox1
        .LinkedCell = ""
        .ListFillRange = ""
        For Each myCell In rngItems.Cells
            If Trim(myCell) <> "" Then
                .AddItem myCell.Value
            End If
        Next myCell
    End With

    Me.ListBox1.MultiSelect = fmMultiSelectMulti
    Me.ListBox2.MultiSelect = fmMultiSelectMulti

End Sub

Thank you in advance for any support (explanations of what your answer means is greatly appreciated as I am still in the early stages of learning VBA)

Thanks

gnikcin gnikcin · Accepted Answer · 2017-07-20T10:31:31

Managed to fix it.... For those that need the answer, I put the below in to the end of the DoFilter:

 Range("D6").Select
    Range(Selection, Selection.End(xlDown)).SpecialCells(xlCellTypeVisible).Select
Selection.Name = "ItemList"

Listbox selection from a filtered list (VBA)

1 Answers