Best code to sort a table column in a VBA macro

[JenniferMurphy]

I am working on a macro that will do some processing on the data in a table. When it is done, it might want to sort the table on one column. Having no idea how to do that, I recorded 3 macros with different portions of the table column selected.

This was my first try. I selected one cell in the column.

Sub Macro1()

' Recorded with one cell in column selected

    Range("D15").Select
    ActiveWorkbook.Worksheets("Example").ListObjects("TblExample").Sort.SortFields. _
        Clear
    ActiveWorkbook.Worksheets("Example").ListObjects("TblExample").Sort.SortFields. _
        Add2 Key:=Range("D15:D22"), SortOn:=xlSortOnValues, Order:=xlDescending, _
        DataOption:=xlSortTextAsNumbers
    With ActiveWorkbook.Worksheets("Example").ListObjects("TblExample").Sort
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
End Sub

This was my second try. I selected the column header.

Sub Macro2()

' Recorded with column header selected

    Range("TblExample[[#Headers],[WtdRtg]]").Select
    ActiveWorkbook.Worksheets("Example").ListObjects("TblExample").Sort.SortFields. _
        Clear
    ActiveWorkbook.Worksheets("Example").ListObjects("TblExample").Sort.SortFields. _
        Add2 Key:=Range("TblExample[[#Headers],[#Data],[WtdRtg]]"), SortOn:= _
        xlSortOnValues, Order:=xlDescending, DataOption:=xlSortTextAsNumbers
    With ActiveWorkbook.Worksheets("Example").ListObjects("TblExample").Sort
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
End Sub

This was my third try. I selected the entire column, but not the header. This looks like the best option. But do I need all of that code?

Sub Macro3()

' Recorded with entire column (without header) selected

    Range("TblExample[WtdRtg]").Select
    ActiveWorkbook.Worksheets("Example").ListObjects("TblExample").Sort.SortFields. _
        Clear
    ActiveWorkbook.Worksheets("Example").ListObjects("TblExample").Sort.SortFields. _
        Add2 Key:=Range("TblExample[WtdRtg]"), SortOn:=xlSortOnValues, Order:= _
        xlDescending, DataOption:=xlSortTextAsNumbers
    With ActiveWorkbook.Worksheets("Example").ListObjects("TblExample").Sort
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
End Sub

Thanks for any help.

 

[bebo021999]

Among the three codes you provided, "Macro3" can be considered the most dynamic.
- Macro3 operates on a specific column ("WtdRtg") of the table ("TblExample"), without relying on a fixed range or number of rows. It selects the entire column dynamically based on the table's structure. This allows the code to adapt to changes in the size or location of the data within the column.
- The code uses structured references ("TblExample[WtdRtg]") instead of hardcoded cell references. Structured references in Excel tables are dynamic and adjust automatically when the table size or structure changes. This enhances the flexibility and adaptability of the code.
- By using the "Range" object's dynamic selection based on the table structure, the code can handle varying data sizes and adapt to changes in the table's layout without requiring manual adjustments to the code.
If put it in worksheet_change event, table will be sorted instantly, no subrourtine activate

 

[kevin9999]

As far as minimal code goes, and using your Macro3 as the basis, try the following:

Sub JMurph()
    With Worksheets("Example").ListObjects("TblExample").Sort
        .SortFields.Clear
        .SortFields.Add Key:=Worksheets("Example").Range("TblExample[WtgRtg]"), Order:=2
        .Header = xlYes
        .Apply
    End With
End Sub

 

[JenniferMurphy]

As far as minimal code goes, and using your Macro3 as the basis, try the following:

Sub JMurph()
    With Worksheets("Example").ListObjects("TblExample").Sort
        .SortFields.Clear
        .SortFields.Add Key:=Worksheets("Example").Range("TblExample[WtgRtg]"), Order:=2
        .Header = xlYes
        .Apply
    End With
End Sub

I like the compactness. Is there anything that could go wrong that this code does not catch that the more elaborate code does? I like tight code, but I like correct results even better! Thanks

 

[kevin9999]

Is there anything that could go wrong that this code does not catch that the more elaborate code does?

I don't believe so, it's very specific as far as capturing the ranges/columns involved - although it would take significant testing to try to crash it (perhaps with different data types in the WtgRtg column?).

 

[JenniferMurphy]

I don't believe so, it's very specific as far as capturing the ranges/columns involved - although it would take significant testing to try to crash it (perhaps with different data types in the WtgRtg column?).

The data in the WtdRtg column will always be numeric (z scores). That column is the result of (output from) the macro. The numbers will typically range from +100 to -300.

 

[kevin9999]

Should be fine then. Some of your code in Macro3 (e.g. .SortMethod = xlPinYin) are the default values for Sort anyway & don't necessarily need to be specified.

 

[JenniferMurphy]

Should be fine then. Some of your code in Macro3 (e.g. .SortMethod = xlPinYin) are the default values for Sort anyway & don't necessarily need to be specified.

Your code gets a run-time error '1004' on the second sortfields statement.

Here's the code.

  With Worksheets("Example").ListObjects("TblExample").Sort
    .SortFields.Clear
    .SortFields.Add Key:=Worksheets("Example").Range("TblExample[WtgRtg]"), Order:=2
    .Header = xlYes
    .Apply
  End With

Bebo's code works.

 

[kevin9999]

Your code gets a run-time error '1004' on the second sortfields statement.

Strange, because it worked on a mock-up table I created based on what your Macro 3 code refers to (sheet & table names).
Before:

Book1
	A	B	C
1	HDR1	WtgRtg	HDR3
2	2	4	2
3	3	1	3
4	4	4	4
5	5	7	5
6	6	8	6
7	7	6	7
Example

After:

Book1
	A	B	C
1	HDR1	WtgRtg	HDR3
2	6	8	6
3	5	7	5
4	7	6	7
5	2	4	2
6	4	4	4
7	3	1	3
Example

If you could provide your actual sheet using the XL2BB - Excel Range to BBCode, or share it via Dropbox, Google Drive or similar, I'd like to discover what's causing the problem.

 

[JenniferMurphy]

Here's the minisheet. The macro is in an add-in module.

Weighted Ratings.xlsx
	A	B	C	D	E	F	G	H
3			Table name	TblExample
4			Maximum Final Rating
5			Sort on WtdRtg?
6			Number of products	10
7
8				Header Sync	Price	Rank	0-10 Rtg	1-5 Rtg
9				Data Type	Num	Num	Num	Num
10				Rating Order	LoHi	LoHi	HiLo	HiLo
11				Weight	3	2	1
12		Product	Prod Rank	WtdRtg	Price	Rank	0-10 Rtg	1-5 Rtg
13		B	1	+100.00	$300	1	9.7
14		D	3	+75.24	$350	1	9.9
15		A	2	+84.78	$250	6	9.8
16		F	5	+23.50	$400	3	9.9
17		C	4	+32.79	$300	7	9.5
18		E	6	-12.32	$350	8	9.4
19		H	7	-32.16	$450	3	9.2
20		J	8	-77.52	$500	3	8.8
21		I	10	-109.42	$450	10	9.0
22		G	9	-84.90	$400	9	8.5
23		Mean		-0.00	$375	5.1	9.4
24		Std Dev		-75.17
Example

Cell Formulas
Range	Formula
D6	D6	=COUNTA(TblExample[Product])
E8	E8	=TblExample[[#Headers],[Price]]
F8	F8	=TblExample[[#Headers],[Rank]]
G8	G8	=TblExample[[#Headers],[0-10 Rtg]]
H8	H8	=TblExample[[#Headers],[1-5 Rtg]]
C13:C22	C13	=RANK.EQ([@WtdRtg],[WtdRtg],0)
E23	E23	=SUBTOTAL(101,[Price])
F23	F23	=SUBTOTAL(101,[Rank])
G23	G23	=SUBTOTAL(101,[0-10 Rtg])
D23	D23	=SUBTOTAL(101,[WtdRtg])
D24	D24	=-STDEV.S(TblExample[WtdRtg])

I'm going to bed. More on this tomorrow.