Filter based on Combobox.Value in Listview

Question

How can I filter the data based on selected Combobox.Value in ListView? The criterias are in E column. I want to filter for E Column in ListView.

I have tried what I found but nothing works. Maybe someone has an answer for this.

Image

Private Sub UserForm_Initialize()
    Dim dU1 As Object, cU1 As Variant, iU1 As Long, lrU As Long
    Dim i As Integer

    Set dU1 = CreateObject("Scripting.Dictionary")
    lrU = Worksheets("Data").Cells(Rows.Count, 1).End(xlUp).Row
    cU1 = Worksheets("Data").Range("A2:A" & lrU)
    For iU1 = 1 To UBound(cU1, 1)
        dU1(cU1(iU1, 1)) = 1
    Next iU1



    For i = 0 To dU1.Count - 1
        ComboBox1.AddItem dU1.Keys()(i)
    Next

End Sub


Private Sub LoadListView()

    Dim wksSource As Worksheet
    Dim rngData As Range
    Dim rngCell As Range
    Dim LstItem As ListItem
    Dim RowCount As Long
    Dim ColCount As Long
    Dim i As Long
    Dim j As Long
    
    'Set the source worksheet
    Set wksSource = Worksheets("Data")
    

    Set rngData = wksSource.Range("A1").CurrentRegion
    

    For Each rngCell In rngData.Rows(1).Cells
        Me.ListView1.ColumnHeaders.Add Text:=rngCell.Value, Width:=90
    Next rngCell
    
   
    RowCount = rngData.Rows.Count
    
   
    ColCount = rngData.Columns.Count
    
  
    For i = 2 To RowCount
        Set LstItem = Me.ListView1.ListItems.Add(Text:=rngData(i, 1).Value)
        For j = 2 To ColCount
            LstItem.ListSubItems.Add Text:=rngData(i, j).Value
        Next j
    Next i
    
End Sub

Private Sub UserForm_Activate()


    With Me.ListView1
        .Gridlines = True
        .HideColumnHeaders = False
        .View = lvwReport
    End With
    
    Call LoadListView

End Sub

Private Sub ComboBox1_Change()
Dim lLastRow As Long
    Dim i As Integer

    
    lLastRow = Cells(Rows.Count, 1).End(xlUp).Row

    For i = 1 To lLastRow
        If Worksheets("Data").Cells(i, 1) = ComboBox1.Text Then
            
        End If
    Next
End Sub

Answer 1

Parameterize Sub LoadListView()

Option Explicit

Private Sub UserForm_Activate()
    With Me.ListView1
        .Gridlines = True
        .HideColumnHeaders = False
        .View = lvwReport
    End With
    Call LoadListView("")
End Sub

Private Sub LoadListView(s As String)
    Dim Lstitem As ListItem
    Dim rngData As Range, rngCell As Range
    Dim i As Long, j As Long
    
    'Set the source worksheet
    Me.ListView1.ListItems.Clear
    With Worksheets("Data")
        Set rngData = .Range("A1").CurrentRegion
        For Each rngCell In rngData.Rows(1).Cells
            Me.ListView1.ColumnHeaders.Add Text:=rngCell.Value, Width:=90
        Next
    End With
    
    With Me.ListView1
        For i = 2 To rngData.Rows.Count
            If s = "" Or rngData(i, 1) = s Then
                Set Lstitem = .ListItems.Add(Text:=rngData(i, 1).Value)
                For j = 2 To rngData.Columns.Count
                    Lstitem.ListSubItems.Add Text:=rngData(i, j).Value
                Next j
            End If
        Next i
    End With
End Sub


Private Sub ComboBox1_Change()
    Call LoadListView(ComboBox1.Text)
End Sub