Excel VBA筛选数据无法完整导入用户表单列表问题求助

需求：根据Excel自定义表格（ListObject）第2列的状态值筛选项目——状态为active对应值a，非active对应值n，需将筛选后的第1列项目名称导入用户表单列表。

问题：编写VBA代码完成筛选后，Excel界面内的筛选结果显示正常，但通过SpecialCells(xlCellTypeVisible)方法仅能将部分数据导入用户表单列表。

解决方法：通过遍历单元格并将可见行数据存入数组的方式解决问题，以下是原问题代码及解决方案代码：

原问题代码 #

Worksheets("list").ListObjects("projects").AutoFilter.ShowAllData
hide_blank_cells_projekti


If active= True And not_active = True Then
        
            Worksheets("list").ListObjects("projects").ListColumns(2).DataBodyRange.AutoFilter _
            Field:=2, Criteria1:="a", Operator:=xlOr, Criteria2:="n", Operator:=xlFilterValues

ElseIf active= True And not_active = False Then

            Worksheets("list").ListObjects("projects").ListColumns(2).DataBodyRange.AutoFilter _
            Field:=2, Criteria1:="a", Criteria2:="A", Operator:=xlFilterValues

ElseIf active= False And not_active = True Then

            Worksheets("list").ListObjects("projects").ListColumns(2).DataBodyRange.AutoFilter _
            Field:=2, Criteria1:="n", Criteria2:="N", Operator:=xlFilterValues

ElseIf active = False And not_active = False Then

            Worksheets("list").ListObjects("projects").ListColumns(2).DataBodyRange.AutoFilter _
            Field:=2, Criteria1:="<>n", Operator:=xlAnd, Criteria2:="<>a", Operator:=xlFilterValues

End If

'    ClearBlankCellsInColumn
project_list.List = Worksheets("list").ListObjects("projects").DataBodyRange.Columns(1).SpecialCells(xlCellTypeVisible).Value

解决方案代码 #

Dim cell As Range
Dim projects_array() As Variant

i = 0
ReDim projects_array(i)

For Each cell In Worksheets("list").ListObjects("projects").ListColumns(1).DataBodyRange
    If Not cell.EntireRow.Hidden Then
        projects_array(i) = cell.Value
        i = i + 1
    End If
    ReDim Preserve projects_array(i)
Next cell

ReDim Preserve projects_array(UBound(projects_array) - 1)
project_list.List = projects_array

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