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?
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
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
You could shorten to:
With ActiveWorkbook.ActiveSheet.ListObjects(1)
.Range.Sort Key1:=.ListColumns(3), Key2:=.ListColumns(4), Header:=xlYes
End With
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
