vba - 如何在用户表单列表框中显示过滤的行

Question

我在用户表单中有一个 Excel 工作表和一个列表框。
当我通过单击用户窗体上的按钮过滤工作表并更新列表框时，我会看到列表框中的所有行。我的意思是 listbox1 显示所有单元格(过滤器 + 无过滤器)。
我更新列表框的代码:

Private Sub CommandButton1_Click()
    CommandButton10.Visible = True
    insertlist1.Visible = True
    ListBox1.Visible = True
    ListBox1.RowSource = "'NEWPRJ'!D7:D46"
End Sub

Answer 1

下面的代码在“NEWPRJ”工作表中将过滤器应用于 Range(“D7:D46”) 后仅读取可见单元格，并将它们保存到 MyArr数组，然后在 ListBox1 中显示它们User_Form 中的列表框。

使用 .SpecialCells(xlCellTypeVisible)只允许读取可见单元格。

Option Explicit

Private Sub CommandButton1_Click()

Dim cell As Range
Dim MyArr  As Variant, i As Long

' intialize array to high number of elements at start
ReDim MyArr(0 To 10000)

' work on sheets "NEWPRJ" according to PO
With Sheets("NEWPRJ")
    ' scan each cell in Range "D7:D46" only on visible cells (cells that are visible after the filter was applied)
    For Each cell In .Range("D7:D46").SpecialCells(xlCellTypeVisible)
        MyArr(i) = cell.Value ' read all visible cells to array
        i = i + 1
    Next cell
    ' reduce array size to populated elements only
    ReDim Preserve MyArr(0 To i - 1)

    ' populate listbox with array
    ListBox1.List = MyArr
End With

End Sub