VBA process to create readable sub-total blocks in Excel

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
donohara1Asked:
Who is Participating?
 
StephenJRConnect With a Mentor Commented:
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

Open in new window

0
 
donohara1Author Commented:
well done and Thanks.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.