Cannot apply a VBA Autofilter to a table which I export to a CSV

Admin 1
Viewed 101

I have a table with rows that have cells with "" blank?! characters So I try the following to filter those rows

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

But I get the whole table when I do the following

'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

and not the filtered table which I can see that is applied already to the worksheet

The whole sub is 

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

How can I pass to Array the Filtered table and not the whole table?

vba excel
1 Answers

Try something like the code below (explanations inside the codes' comments):

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
user_63443630
Related