2

I have a table ReportContent_Table, I want to do the followings with VBA:

  1. filter table by specific columns
  2. sort table by specific column
  3. 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): enter image description here

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. enter image description here

What's wrong with my code? Thanks.

Kevin Lee
  • 401
  • 3
  • 9
  • 22
  • 2
    You can't read the `.Value` of a multi-area `Range` into an array like that... you have to loop. – BigBen Sep 24 '20 at 02:57
  • https://stackoverflow.com/questions/25654992/excel-vba-create-two-dimensional-array-from-multiple-range-variables-no-duplic – BigBen Sep 24 '20 at 03:00
  • https://stackoverflow.com/questions/30940717/saving-multiple-excel-ranges-into-a-2-dimensional-array-not-all-at-the-same – BigBen Sep 24 '20 at 03:00
  • https://stackoverflow.com/questions/11995761/how-do-i-copy-a-filtered-range-into-an-array-excel-vba – BigBen Sep 24 '20 at 03:04

0 Answers0