Sort an Excel table by values of a dynamic array VBA

Question

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.

It's happening because you are creating a 2-D array, and Autofilter takes an 1-D array. — Mikku
– Mikku, Commented Aug 2, 2019 at 9:15

Mikku · Accepted Answer · 2019-08-02 09:18:18Z

1

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

answered Aug 2, 2019 at 9:18

Mikku

6,7043 gold badges19 silver badges39 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Andreas Hild Over a year ago

Thanks man for the heads-up with the 2-D Array I did not know that, the code solved my problem. Happy Friday, cheers!

Mikku Over a year ago

Cheers Mate :) Glad to help

Collectives™ on Stack Overflow

Sort an Excel table by values of a dynamic array VBA

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related