Sorting a table - seems much harder than it should be?

[TheRedCardinal]

In my workbook I have several sheets that contain tables in the same format.

I want to sort them by the columns in B and D - there are headers. B is a date, D is a number - so both in ascending sort order.

I adjusted some code I found and came up with this:

  With WS2
        
        Set AllData = .UsedRange
        
        With .Sort
        
            .SortFields.Clear
            .SortFields.Add Key:=Range("B1"), SortOn:=xlSortValues, Order:=xlAscending, DataOption:=xlSortNormal
            .SortFields.Add Key:=Range("D1"), SortOn:=xlSortValues, Order:=xlAscending, DataOption:=xlSortNormal
            .SetRange (Range(AllData))
            .Header = xlYes
            .MatchCase = False
            .Orientation = xlTopToBottom
            .SortMethod = xlPinYin
            .Apply
        
        End With

The main issues I'm having is in defining the range as it adjusts with every sheet (in terms of rows) so I tried to set it as UsedRange, but I keep getting errors in the .SetRange line depending on how I phrase the line.

As above, it says "Method "Range" of object global failed - but I also get requires object errors when I change the way I'm trying to refer to AllData as a range.

Do I really need to go in and define Last Row and Last Column Variables and define my range that way, using .Cells in the .SetRange line?

 

[RoryA]

AllData is a range, so you don't put it inside a Range call. But if you have a Table, you should be using the Listobject directly to manipulate it.

 

[TheRedCardinal]

I used "Table" in the general sense rather than the Excel sense - later on in the procedure the data gets converted to a table, so would it be better to do that at the start? It's easier to sort a ListObject?

 

[RoryA]

Easier, yes, but probably slower. Try just amending that code to:

.SetRange AllData

 

[TheRedCardinal]

That seems to do the trick.

Thanks Rory.

 

[RoryA]

Glad to help.