CODEWITHSUNDEEP
arraysvbalistsortingdynamic
Andreas Hild
Andreas Hild

Reputation: 79

Sort an Excel table by values of a dynamic array VBA

I'm trying to sort an Excel Table by values of an Array.

I have data in this format which I want to filter a table based on. 

India 
USA
China

I have the country column in field 4, the table can be sorted in this way

Worksheets("Pilot").ListObjects("Table1").Range.AutoFilter
Field:=4, _
Criteria1:=Array("India", "USA", "China"), _
Operator:=xlFilterValues

However I want to sort the table by strings from an Array since I have 100+ values that I want to sort the table by.

I have tried to create an Array but I can't get the table filtered by the Array. The Array gets created but the table does just filter the first value of the Array. The Array becomes one dimensional I don't know if that is a problem? So if I want to get the 3rd value of my Array I have to call: 

Myarray(3,1)

I have tried this 

Dim MyArray() As Variant
StartRow = 13
LastRow = Cells(StartRow, "X").CurrentRegion.Rows.count
MyArray = Range(Cells(StartRow, "X"), Cells(LastRow, "X")).Value

Worksheets("Pilot").ListObjects("Table1").Range.AutoFilter _
Field:=4, _
Criteria1:= MyArray, _
Operator:=xlFilterValues

This does just filter the table by the first value of the Array.

So the expected result is to get text values from a column and then sort a table based on these values.

Thanks a lot for any help.

Upvotes: 0

Views: 479

Answers (1)

Mikku
Mikku

Reputation: 6654

Problem:

  • When we assign values to an Array using Range object, the resulting Array is a 2-D array. But Range.Autofilter takes an 1-D array.

Try This:

Dim MyArray() As Variant
Dim arr() As Variant
StartRow = 13

LastRow = Cells(StartRow, "X").CurrentRegion.Rows.Count
MyArray = Range(Cells(StartRow, "X"), Cells(LastRow, "X")).Value

ReDim arr(1 To UBound(MyArray))

For i = LBound(MyArray) To UBound(MyArray)
        arr(i) = MyArray(i, 1)
Next

Worksheets("Pilot").ListObjects("Table1").Range.AutoFilter Field:=4, Criteria1:=arr, Operator:=xlFilterValues

Upvotes: 1

Related Questions