Excel VBA Sort Largest to Smallest with Row Groups

[Biz]

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

	A	B	C	D	E	F	G	H	I
1	Sample
2	Store No	Store	Amount
3		Parramatta	50
4	60	Store2	20
5	70	Store3	30
6	80	Store4	100
7		Westmead	3
8	90	store 5	1
9	100	Store 6	2
10
11
12						Desired Result
13						Store No	Store	Amount
14						80	Store4	100
15							Parramatta	50
16						60	Store2	20
17						70	Store3	30
18							Westmead	3
19						90	store 5	1
20						100	Store 6	2
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

Zelle Formel 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

	A	B	C	D	E	F	G	H	I
1	Sample
2	Store No	Store	Amount
3		Parramatta	50
6	80	Store4	100
7		Westmead	3
10
11
12						Desired Result
13						Store No	Store	Amount
14						80	Store4	100
15							Parramatta	50
18							Westmead	3
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

Zelle Formel 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]

Hi,

My desire result should be as below.
Sheet1

	A	B	C
1	Desired Result
2	Store No	Store	Amount
3	80	Store4	100
4		Parramatta	50
7		Westmead	3
10

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

Formeln der Tabelle

Zelle Formel C4 =SUM(C5:C6) C7 =SUM(C8:C9) <tbody> </tbody>

<tbody>
</tbody>

Kind Regards

Biz

 

[ZVI]

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

 

[Biz]

Hi,
I'm attaching link so that you can have the file.

http://www.mediafire.com/file/a726y...st_to_Smallest_with_Row_Groups_Demo.xlsm/file

We are very close to result but not sure why it's not 100% correct.

Kind Regards.

Biz

 

[ZVI]

Well, I see the already outlined source data, try this with clearing of that outline:

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

 

[Biz]

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