Sorting a table in Excel using VBA?

Question

I have tried to sort a table using the following VBA code. The code does select the correct column, and the column filter does get a small arrow in it, indicating that it is sorted. But the rows do not sort. They stay unsorted.

What is wrong?

Sub SortTableTest()
    Dim tbl As ListObject
    Dim columnToSortBy As Range

    Set tbl = Sheets("PB").ListObjects("AI")
    Set columnToSortBy = tbl.ListColumns(9).Range

    'Sort table
    With tbl.Sort
        .SortFields.Clear
        .SortFields.Add columnToSortBy, xlDescending
        .Header = xlYes
        .MatchCase = False
        .Apply
    End With
End Sub

Additionally, if you know how I can reference the column I am interested in by name instead of by column, that would be great. — user1283776
– user1283776, Commented Oct 22, 2013 at 8:36
Since table is often a misnomer in Excel a lot of people are likely looking for how to sort data in general: Sort data based on column — Juuso Nykänen
– Juuso Nykänen, Commented Feb 12, 2024 at 15:58

Kazimierz Jawor · Accepted Answer · 2013-10-22 08:52:03Z

5

First, you missed one parameter in .SortFields.Add method. What you need is:

.SortFields.Add columnToSortBy, xlSortOnValues, xlDescending

Second, question from you comment. Try with this kind of References:

Set columnToSortBy = Range(tbl.Name & "[[#All],[column name here]]")

answered Oct 22, 2013 at 8:52

Kazimierz Jawor

19.1k7 gold badges38 silver badges58 bronze badges

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

2 Comments

Venu Over a year ago

When I use Range as mentioned above, I get the error method 'Range' of object _Global failed? I know, this is Avery old thread...may be somebody could help me?

Kazimierz Jawor Over a year ago

@Venu, you should somehow show your code. Best idea is to post your own question.

Collectives™ on Stack Overflow

Sorting a table in Excel using VBA?

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related