如何基于ComboBox选中值过滤ListView的E列数据？

1. 修正ComboBox初始化（加载E列唯一值） #

因为过滤条件对应数据源的E列，所以ComboBox应显示E列的唯一选项，修改UserForm_Initialize中的数据源范围：

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
    ' 修改为E列（第5列）的数据源
    cU1 = Worksheets("Data").Range("E2:E" & lrU)
    For iU1 = 1 To UBound(cU1, 1)
        ' 跳过空值
        If cU1(iU1, 1) <> "" Then
            dU1(cU1(iU1, 1)) = 1
        End If
    Next iU1

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

2. 重构LoadListView支持过滤 #

修改LoadListView，添加过滤参数，同时每次加载前清空ListView避免重复列头和数据：

Private Sub LoadListView(Optional strFilter As String = "")
    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 wksSource = Worksheets("Data")
    Set rngData = wksSource.Range("A1").CurrentRegion
    
    ' 清空现有列头和数据
    Me.ListView1.ColumnHeaders.Clear
    Me.ListView1.ListItems.Clear
    
    ' 添加列头
    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
        ' 如果没有过滤条件，或者当前行E列匹配过滤值
        If strFilter = "" Or rngData(i, 5).Value = strFilter Then
            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
        End If
    Next i
End Sub

3. 实现ComboBox过滤逻辑 #

修改ComboBox1_Change事件，调用带过滤参数的LoadListView：

Private Sub ComboBox1_Change()
    ' 传入ComboBox选中的值作为过滤条件，E列匹配则显示
    LoadListView ComboBox1.Text
End Sub

4. 保留窗体激活时的全量加载 #

原UserForm_Activate代码保持不变，确保窗体激活时默认加载全部数据：

Private Sub UserForm_Activate()
    With Me.ListView1
        .Gridlines = True
        .HideColumnHeaders = False
        .View = lvwReport
    End With
    ' 加载全部数据（无过滤）
    LoadListView
End Sub

关键修改说明 #

• 清空ListView：每次加载前清空列头和数据，避免重复添加导致的界面混乱。
• 过滤参数支持：让LoadListView可接受过滤条件，仅加载E列匹配的行数据。
• ComboBox数据源修正：改为绑定E列唯一值，匹配过滤需求。
• 空值处理：初始化ComboBox时跳过E列空值，避免出现无效空选项。

内容的提问来源于stack exchange，提问作者Slayer