Log In
Register
Home
June 15, 2022
Offline
Hi. Im trying to sort a table, I tried recording a macro to get an idea but it produces loads of code just for one click
Any ideas how to sort a table (.ListObjects(1)) by column 3, then by column 4?
See Answer
October 5, 2010
Offline
Hi,
Try this
Sub SortCols()
ActiveSheet.ListObjects("Table1").Sort.SortFields.Clear
Range("Table1").Sort Key1:=Range("Table1[[#All],[Col3]]"), Key2:=Range("Table1[[#All],[Col4]]"), Header:=xlYes
End Sub
Change Col3 and Col4 to the column names. Be default this sorts in Ascending order. If you want something else you can use the Order parameters e.g.
Sub SortCols()
ActiveSheet.ListObjects("Table1").Sort.SortFields.Clear
Range("Table1").Sort Key1:=Range("Table1[[#All],[Col3]]"), Key2:=Range("Table1[[#All],[Col4]]"), Header:=xlYes, Order1:=xlAscending, Order2:=xlDescending
End Sub
regards
Phil
Answers Post
June 15, 2022
Offline
Hi. I'm struggling to incorporate that into my existing With statements
Any ideas?
With ActiveWorkbook.ActiveSheet
With .ListObjects(1)
.Sort Key1:=Range(.Name & "[#All],[Col3]"), Key2:=Range(.Name & "[#All],[Col4]"), Header:=xlYes
End With
End With
Trusted Members
Moderators
November 1, 2018
Offline
You could shorten to:
With ActiveWorkbook.ActiveSheet.ListObjects(1)
.Range.Sort Key1:=.ListColumns(3), Key2:=.ListColumns(4), Header:=xlYes
End With
June 15, 2022
Offline
Great Thanks.
And it also answered my question as to why you keep having to define the same sort range over and over, which was making me very nervous because i dont want to mash up a whole days works. Answer is. You don't
1 Guest(s)
