Excel VBA Sort Largest to Smallest with Row Groups

Biz

Well-known Member
Joined
May 18, 2009
Messages
1,736
Office Version
  1. 2010
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
1Sample
2Store NoStoreAmount
3 Parramatta50
460Store220
570Store330
680Store4100
7 Westmead3
890store 51
9100Store 62
10
11
12 Desired Result
13 Store NoStoreAmount
14 80Store4100
15 Parramatta50
16 60Store220
17 70Store330
18 Westmead3
19 90store 51
20 100Store 62
21
22
23

<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>
</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
1Sample
2Store NoStoreAmount
3 Parramatta50
680Store4100
7 Westmead3
10
11
12 Desired Result
13 Store NoStoreAmount
14 80Store4100
15 Parramatta50
18 Westmead3
21
22
23

<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>
</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
 

Biz

Well-known Member
Joined
May 18, 2009
Messages
1,736
Office Version
  1. 2010
Platform
  1. Windows
Hi,

My desire result should be as below.
Sheet1

ABC
1Desired Result
2Store NoStoreAmount
380Store4100
4 Parramatta50
7 Westmead3
10

<colgroup><col style="width: 30px;"><col style="width: 98px;"><col style="width: 75px;"><col style="width: 64px;"></colgroup><tbody>
</tbody>

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

<tbody>
</tbody>

<tbody>
</tbody>

Kind Regards

Biz
 

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.

ZVI

MrExcel MVP
Joined
Apr 9, 2008
Messages
3,875
Office Version
  1. 2019
  2. 2016
  3. 2010
Platform
  1. Windows
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:

ZVI

MrExcel MVP
Joined
Apr 9, 2008
Messages
3,875
Office Version
  1. 2019
  2. 2016
  3. 2010
Platform
  1. Windows
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

Biz

Well-known Member
Joined
May 18, 2009
Messages
1,736
Office Version
  1. 2010
Platform
  1. Windows
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:

Forum statistics

Threads
1,176,078
Messages
5,901,271
Members
434,884
Latest member
ares0027

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
Top