Forum Discussion
Rachel1994
Jun 13, 2022Copper Contributor
VBA Format data as table and filter
Hello!
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.
e.g.:
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
.Apply
End With
Any help very much appreciated!
Does this work for you?
tbl.Range.Sort key1:=Range("C1"), order1:=xlAscending, key2:=Range("D1"), order2:=xlAscending, Header:=xlYes
- Subodh_Tiwari_sktneerSilver Contributor
Does this work for you?
tbl.Range.Sort key1:=Range("C1"), order1:=xlAscending, key2:=Range("D1"), order2:=xlAscending, Header:=xlYes
- Rachel1994Copper ContributorYes this works perfectly, thanks so much for your help!
- Subodh_Tiwari_sktneerSilver Contributor
You're welcome Rachel1994! Glad it worked as desired.