SOLVED

VBA Format data as table and filter

Occasional Contributor

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!

3 Replies
best response confirmed by Rachel1994 (Occasional Contributor)
Solution

@Rachel1994 

 

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.