VBA Format data as table and filter

Occasional Contributor



I'm writing a macro to make a data output look nicer for users. I have formatted the data as a table, and now I want to sort the data on two things: first Supplier Name, then Product Description (These are the headers).


I set up the table as follows:


Dim tbl As ListObject
Dim rng As Range

Set rng = Range(Range("A1"), Range("a1").SpecialCells(xlLastCell))
Set tbl = ActiveSheet.ListObjects.Add(xlSrcRange, rng, , xlYes)
tbl.TableStyle = "TableStyleLight1"


I've tried various bits of code to sort this but keep getting errors and not sure where I'm going wrong. 


With tbl.Sort
.SortFields.Add Key:=Range("C1"), Order:=xlAscending        'This sorts on supplier name
.SortFields.Add Key:=Range("D1"), Order:=xlAscending        'This sorts on product description
.SetRange (rng)
.Header = xlYes
End With


Any help very much appreciated!

3 Replies
best response confirmed by Rachel1994 (Occasional Contributor)



Does this work for you?

tbl.Range.Sort key1:=Range("C1"), order1:=xlAscending, key2:=Range("D1"), order2:=xlAscending, Header:=xlYes



Yes this works perfectly, thanks so much for your help!

You're welcome @Rachel1994! Glad it worked as desired.