Sorting a Table

You can sort the data in the table in the same way as you sort a range, or by clicking one of the dropdowns beside the field names and selecting one of the sort options. If you record a sort based on the Name field using the drop-down beside the field name, you will get code like the following:

ActiveWorkbook.Worksheets("Sheet1").ListObjects("Table1").Sort.SortFields.Clear ActiveWorkbook.Worksheets("Sheet1").ListObjects("Table1").Sort.SortFields.Add _ Key:=Range("Table1[[#All],[Name]]"), SortOn:=SortOnValues, Order:= _ xlAscending, DataOption:=xlSortNormal With ActiveWorkbook.Worksheets("Sheet1").ListObjects("Table1").Sort .SetRange Range("Table1[#All]") .Header = xlYes

.MatchCase = False .Orientation = xlTopToBottom .SortMethod = xlPinYin .Apply End With

The code for sorting a table is similar to the code for sorting a range. Instead of the Worksheet object being the parent of the Sort object, the ListObject object is the parent. Notice also that there are new ways to reference data ranges. The SetRange method uses Range("Table1[#All]") to specify all of the data in the table. The SortField object key is specified using Range("Table1[[#All],[Name]]").

0 0

Post a comment