Sort multiple data sets in the same sheet

[kapperson]

Hello,
I've done some searching and haven't been able to find a way to do this yet.
I have multiple data sets (formatted as tables but not true Excel tables) that have the same header in Column B and a Total column to signify the top row and the last row.
I'm trying to find a way to code a macro that goes through and sorts all of these tables independently based on the values of a different column - so in the sample file below, this would be column "4."

[TABLE="width: 345"]
<colgroup><col><col span="4"></colgroup><tbody>[TR]
[TD]Table Header[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD="align: right"]5[/TD]
[TD]J[/TD]
[TD="align: right"]75[/TD]
[TD="align: right"]375[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD="align: right"]10[/TD]
[TD]K[/TD]
[TD="align: right"]500[/TD]
[TD="align: right"]2500[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD="align: right"]20[/TD]
[TD]I[/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]150[/TD]
[/TR]
[TR]
[TD]D[/TD]
[TD="align: right"]30[/TD]
[TD]P[/TD]
[TD="align: right"]27[/TD]
[TD="align: right"]135[/TD]
[/TR]
[TR]
[TD]E[/TD]
[TD="align: right"]40[/TD]
[TD]O[/TD]
[TD="align: right"]47[/TD]
[TD="align: right"]235[/TD]
[/TR]
[TR]
[TD]F[/TD]
[TD="align: right"]50[/TD]
[TD]S[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]25[/TD]
[/TR]
[TR]
[TD]Total[/TD]
[TD="align: right"]155[/TD]
[TD]-[/TD]
[TD="align: right"]684[/TD]
[TD="align: right"]3420[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Table Header[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD="align: right"]100[/TD]
[TD]R[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]35[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD="align: right"]200[/TD]
[TD]S[/TD]
[TD="align: right"]25[/TD]
[TD="align: right"]125[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD="align: right"]300[/TD]
[TD]A[/TD]
[TD="align: right"]13[/TD]
[TD="align: right"]65[/TD]
[/TR]
[TR]
[TD]F[/TD]
[TD="align: right"]400[/TD]
[TD]Y[/TD]
[TD="align: right"]19[/TD]
[TD="align: right"]95[/TD]
[/TR]
[TR]
[TD]D[/TD]
[TD="align: right"]500[/TD]
[TD]X[/TD]
[TD="align: right"]54[/TD]
[TD="align: right"]270[/TD]
[/TR]
[TR]
[TD]E[/TD]
[TD="align: right"]600[/TD]
[TD]Z[/TD]
[TD="align: right"]658[/TD]
[TD="align: right"]3290[/TD]
[/TR]
[TR]
[TD]G[/TD]
[TD="align: right"]700[/TD]
[TD]K[/TD]
[TD="align: right"]26[/TD]
[TD="align: right"]130[/TD]
[/TR]
[TR]
[TD]H[/TD]
[TD="align: right"]800[/TD]
[TD]T[/TD]
[TD="align: right"]95[/TD]
[TD="align: right"]475[/TD]
[/TR]
[TR]
[TD]Total[/TD]
[TD="align: right"]2100[/TD]
[TD]-[/TD]
[TD="align: right"]776[/TD]
[TD="align: right"]4485[/TD]
[/TR]
</tbody>[/TABLE]


The number of rows in the table will not always be the same, and there will likely be more added over time, so variable references would be ideal.
I assume what the macro will have to do is loop through the cells and find the header, the total, and then sort the columns in between, I'm just not entirely sure how to code for that.

Any help would be appreciated!

 

[hiker95]

The only problem I'm having now is that it's including the value in the total row in the sort, so my total row ends up in the middle of the table. Any thoughts on how to get around that?

kapperson,

I would suggest that you manually put Total in the below gray cells in column DW, per the following screenshots:

Excel 2007
	DW
10	Sort
11	54.1
12	72.8
13	74.2
14	81.7
15	101.0
16	85.3
17	175.2
18	181.0
19	35.4
20	152.7
21	36.4
22	67.0
23	59.0
24	23.0
25	Total
Sheet1

Excel 2007
	DW
28	Sort
29	54.1
30	72.8
31	74.2
32	81.7
33	101.0
34	Total
Sheet1

 

[hiker95]

kapperson,

After you manually put Total in the below gray cells in column DW, then try running the next macro.

Sub kapperson_V3()
' hiker95, 07/06/2017, ME1012860
Dim Area As Range, sr As Long, er As Long
Application.ScreenUpdating = False
With ActiveSheet
  For Each Area In Range("DW1", Range("DW" & .Rows.Count).End(xlUp)).SpecialCells(xlCellTypeConstants).Areas
    With Area
      sr = .Row + 1
      er = sr + .Rows.Count - 3
      Range("DW" & sr & ":DW" & er).Sort key1:=Range("DW" & sr), order1:=1
    End With
  Next Area
End With
Application.ScreenUpdating = True
End Sub

 

[kapperson]

kapperson,

After you manually put Total in the below gray cells in column DW, then try running the next macro.

Sub kapperson_V3()
' hiker95, 07/06/2017, ME1012860
Dim Area As Range, sr As Long, er As Long
Application.ScreenUpdating = False
With ActiveSheet
  For Each Area In Range("DW1", Range("DW" & .Rows.Count).End(xlUp)).SpecialCells(xlCellTypeConstants).Areas
    With Area
      sr = .Row + 1
      er = sr + .Rows.Count - 3
      Range("DW" & sr & ":DW" & er).Sort key1:=Range("DW" & sr), order1:=1
    End With
  Next Area
End With
Application.ScreenUpdating = True
End Sub

This looks like it fixed the problem! It's working perfectly now.
Thank you so much for your help!!

 

[hiker95]

kapperson,

Thanks for the feedback.

You are very welcome. Glad I could help.

And, come back anytime.