Forum Discussion

Rachel1994's avatar
Rachel1994
Copper Contributor
Jun 13, 2022
Solved

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!

Resources