Solved

# VBA process to create readable sub-total blocks in Excel

Posted on 2011-09-28
178 Views
I have a data array in an Excel sheet. The data is organized by a group code and the rows are sorted by this code.I want to insert a summary block (group averages) after each group. I am familiar with the Subtotal function, but I want to add in 2 blank lines to aid in readability. I am interested in VBA code procedure, so that I may add in more data summarizing fuctions in the future.

Thank you.
Test-Combined-FL-Sept-24.xls
0
Question by:donohara1

LVL 24

Accepted Solution

StephenJR earned 500 total points
ID: 36717934
This is a pretty workmanlike solution, but I think it does what you want:
``````Sub x()

Dim r1 As Long, r2 As Long

r1 = 7: r2 = 6

Do While Cells(r1, "C") <> ""
If Cells(r1, "C") <> Cells(r1 - 1, "C") Then
Cells(r1, "C").Resize(3).EntireRow.Insert shift:=xlDown
Cells(r1, "C") = "Average " & Cells(r1 - 1, "C")
Cells(r1, "D").Resize(, 16).Formula = "=AVERAGE(D" & r2 & ":D" & r1 - 1 & ")"
r1 = r1 + 4
r2 = r1 - 1
Else
r1 = r1 + 1
End If
Loop

Cells(r1, "C") = "Average " & Cells(r1 - 1, "C")
Cells(r1, "D").Resize(, 16).Formula = "=AVERAGE(D" & r2 & ":D" & r1 - 1 & ")"

End Sub
``````
0

Author Closing Comment

ID: 36816766
well done and Thanks.
0

## Featured Post

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
Improved? Move/Copy Add-in Replacement - How to avoid the annoying, “A formula or sheet you want to move or copy contains the name XXX, which already exists on the destination worksheet.” David Miller (dlmille)  It was one of those days… I wa…
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…