Excel VBA Sort Largest to Smallest with Row Groups

Biz

Well-known Member
Joined
May 18, 2009
Messages
1,773
Office Version
  1. 2021
Platform
  1. Windows
Dear All,

I'm having a very hard time trying sort data in Column C from largest to smallest and also maintaining the row grouping.
Can someone please help me by creating VBA code which can handle this task?

Screenshot below without row grouping
Sheet1

ABCDEFGHI
Store NoStoreAmount
Parramatta
Store2
Store3
Store4
Westmead
store 5
Store 6
Store NoStoreAmount
Store4
Parramatta
Store2
Store3
Westmead
store 5
Store 6

<colgroup><col style="width: 30px;"><col style="width: 75px;"><col style="width: 75px;"><col style="width: 64px;"><col style="width: 64px;"><col style="width: 64px;"><col style="width: 98px;"><col style="width: 75px;"><col style="width: 64px;"><col style="width: 64px;"></colgroup><tbody>
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]1[/TD]
[TD="bgcolor: #FFE699"]Sample[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]2[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]3[/TD]

[TD="align: right"]50[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]4[/TD]
[TD="align: right"]60[/TD]

[TD="align: right"]20[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]5[/TD]
[TD="align: right"]70[/TD]

[TD="align: right"]30[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]6[/TD]
[TD="align: right"]80[/TD]

[TD="align: right"]100[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]7[/TD]

[TD="align: right"]3[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]8[/TD]
[TD="align: right"]90[/TD]

[TD="align: right"]1[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]9[/TD]
[TD="align: right"]100[/TD]

[TD="align: right"]2[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]10[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]11[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]12[/TD]

[TD="bgcolor: #A9D08E"]Desired Result[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]13[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]14[/TD]

[TD="align: right"]80[/TD]

[TD="align: right"]100[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]15[/TD]

[TD="align: right"]50[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]16[/TD]

[TD="align: right"]60[/TD]

[TD="align: right"]20[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]17[/TD]

[TD="align: right"]70[/TD]

[TD="align: right"]30[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]18[/TD]

[TD="align: right"]3[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]19[/TD]

[TD="align: right"]90[/TD]

[TD="align: right"]1[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]20[/TD]

[TD="align: right"]100[/TD]

[TD="align: right"]2[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]21[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]22[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]23[/TD]

</tbody>

Formeln der Tabelle
ZelleFormel
C3=SUM(C4:C5)
C7=SUM(C8:C9)
H15=SUM(H16:H17)
H18=SUM(H19:H20)

<tbody>
</tbody>

<tbody>
</tbody>

Screenshot with row grouping
Sheet1

ABCDEFGHI
Store NoStoreAmount
Parramatta
Store4
Westmead
Store NoStoreAmount
Store4
Parramatta
Westmead

<colgroup><col style="width: 30px;"><col style="width: 75px;"><col style="width: 75px;"><col style="width: 64px;"><col style="width: 64px;"><col style="width: 64px;"><col style="width: 98px;"><col style="width: 75px;"><col style="width: 64px;"><col style="width: 64px;"></colgroup><tbody>
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]1[/TD]
[TD="bgcolor: #FFE699"]Sample[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]2[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]3[/TD]

[TD="align: right"]50[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]6[/TD]
[TD="align: right"]80[/TD]

[TD="align: right"]100[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]7[/TD]

[TD="align: right"]3[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]10[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]11[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]12[/TD]

[TD="bgcolor: #A9D08E"]Desired Result[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]13[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]14[/TD]

[TD="align: right"]80[/TD]

[TD="align: right"]100[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]15[/TD]

[TD="align: right"]50[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]18[/TD]

[TD="align: right"]3[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]21[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]22[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]23[/TD]

</tbody>

Formeln der Tabelle
ZelleFormel
C3=SUM(C4:C5)
C7=SUM(C8:C9)
H15=SUM(H16:H17)
H18=SUM(H19:H20)

<tbody>
</tbody>

<tbody>
</tbody>

Your help would be greatly appreciated.

Kind Regards,

Biz
 
Hi,

My desire result should be as below.
Sheet1

ABC
Store NoStoreAmount
Store4
Parramatta
Westmead

<colgroup><col style="width: 30px;"><col style="width: 98px;"><col style="width: 75px;"><col style="width: 64px;"></colgroup><tbody>
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]1[/TD]
[TD="bgcolor: #A9D08E"]Desired Result[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]2[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]3[/TD]
[TD="align: right"]80[/TD]

[TD="align: right"]100[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]4[/TD]

[TD="align: right"]50[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]7[/TD]

[TD="align: right"]3[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]10[/TD]

</tbody>

Formeln der Tabelle
ZelleFormel
C4=SUM(C5:C6)
C7=SUM(C8:C9)

<tbody>
</tbody>

<tbody>
</tbody>

Kind Regards

Biz
 
Upvote 0

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest
To collapse the rows in a group, just click the minus button [-] at the bottom of that group's bar or click the [1] botton on the left top side of the sheet.
To do the same by the code insert this line:
ActiveSheet.Outline.ShowLevels RowLevels:=1
before that one: Application.ScreenUpdating = True
 
Last edited:
Upvote 0
Well, I see the already outlined source data, try this with clearing of that outline:
Rich (BB code):
Sub Outline()
 
  Dim a()
  Dim i As Long
  Dim Total As Double, v As Double
  Dim Rng As Range
 
  Set Rng = Range("A2:C9")
  a() = Rng.Value
  For i = 2 To UBound(a)
    If Trim(a(i, 1)) = "" Or v = 0 Then
      Total = a(i, 3)
      v = Total
    Else
      v = Round(v - a(i, 3), 3)
    End If
    a(i, 1) = Total
  Next
 
  Application.ScreenUpdating = False
  i = Rng.Columns.Count + 1
  Rng.Columns(i).Insert
  With Rng.Resize(, i)
    .ClearOutline
    .Columns(i).Value = a()
    .Sort .Cells(1, i), xlDescending, Header:=xlYes
    .Columns(i).Delete
    Application.DisplayAlerts = False
    .Worksheet.Outline.AutomaticStyles = True
    .AutoOutline
    Application.DisplayAlerts = True
  End With
  ActiveSheet.Outline.ShowLevels RowLevels:=1
  Application.ScreenUpdating = True
 
End Sub
 
Last edited:
  • Like
Reactions: Biz
Upvote 0
Hi Mate,

You are a genius!
Thank you very much for your help.
I will now try to implement this solution on the business data.
I had to manually to do his month and don't want to do it every month manually.

I need to study your awesome code and make comments to understand this master piece.

Kind Regards,

Biz
 
Last edited:
Upvote 0

Forum statistics

Threads
1,223,214
Messages
6,170,772
Members
452,353
Latest member
strainu

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top