excel - 无法将 VBA 自动过滤器应用于我导出到 CSV 的表

Question

我有一个表格，其中的行包含带有“”空白的单元格？!字符所以我尝试以下过滤这些行

Set tbl = Worksheets("FOR EXPORT").ListObjects("TableName")
'SET AUTOFILTER
tbl.Range.AutoFilter Field:=3, Criteria1:="<>"

但是当我执行以下操作时，我得到了整张 table

'COPY TABLE RANGE TO ARRAY
tblArr = tbl.Range.Value

'SET FILE OUTPUT
fNum = FreeFile()
Open csvFilePath For Output As #fNum

For i = 1 To UBound(tblArr)
    rowArr = Application.Index(tblArr, i, 0)
    csvVal = VBA.Join(rowArr, ";")
    Print #1, csvVal  
Next

而不是我可以看到的已应用于工作表的过滤表

整个子是

Sub saveTableToCSV()

Dim tbl As ListObject
Dim csvFilePath As String
Dim fNum As Integer
Dim tblArr As Variant


Dim rowArr

Dim csvVal


Set tbl = Worksheets("FOR EXPORT").ListObjects("TableName")
'SET AUTOFILTER
tbl.Range.AutoFilter Field:=3, Criteria1:="<>"

'SET PATH
csvFilePath = "C:\Users\FatherNick\Desktop\CSVFile.csv"
'COPY TABLE RANGE TO ARRAY
tblArr = tbl.Range.Value

'SET FILE OUTPUT
fNum = FreeFile()
Open csvFilePath For Output As #fNum

For i = 1 To UBound(tblArr)

    rowArr = Application.Index(tblArr, i, 0)
    csvVal = VBA.Join(rowArr, ";")
    Print #1, csvVal

Next
'RESET FILTER
'tbl.AutoFilter.ShowAllData
Close #fNum
Set tblArr = Nothing
Set rowArr = Nothing
Set csvVal = Nothing
End Sub

如何将过滤后的表而不是整个表传递给数组？

Answer 1

试试下面的代码(代码注释中的解释):

Dim VisRng As Range

' set the new Range object to the visible cells of `tbl.Range`
Set VisRng = tbl.Range.SpecialCells(xlCellTypeVisible)

' copy only the visible cells in range to array
tblArr = VisRng.Value