I have a table ReportContent_Table
, I want to do the followings with VBA:
- filter table by specific columns
- sort table by specific column
- Get the filtered table to an array
Sub Test
Dim ReportContent As ListObject
Set ReportContent = ThisWorkbook.Worksheets("ReportContent").ListObjects("ReportContent_Table")
Dim rng As Range
Set rng = ThisWorkbook.Worksheets("ReportContent").Range("ReportContent_Table[Sorting_Column]")
''' Clear table filter
ReportContent.AutoFilter.ShowAllData
''' Change the Date column to short date format: m/d/yyyy
ReportContent.ListColumns("Date").DataBodyRange.NumberFormat = "m/d/yyyy"
''' Filter table by column 3
ReportContent.Range.AutoFilter Field:=3, Criteria1:="=12/21/2019", Operator:=xlAnd
''' Filter table by column 2
ReportContent.Range.AutoFilter Field:=2, Criteria1:="=A", Operator:=xlAnd
''' Sort the table in ascending order
With ReportContent.Sort
.SortFields.Clear
.SortFields.Add Key:=rng, SortOn:=xlSortOnValues, Order:=xlAscending
.Header = xlYes
.Apply
End With
End Sub
After the above code, my table is being filtered and sorted (table data is blurred):
Then I want to get the table data to an array:
''' Get the report content data to an array
Dim ReportData() As Variant
ReportData = ReportContent.DataBodyRange.SpecialCells(xlCellTypeVisible).Value
I expect ReportData array will contain 25 items, however, it only has 1 item.
What's wrong with my code? Thanks.