How to get my dynamic range to loop correctly

Question

I've got some Excel data grouped in rows and separated by empty cells. I'm trying to sum up each group in a separate table. In order to do this, I'm trying to create a loop to automatically sum these rows but I can't seem to get the dynamic range right.

I'm a beginner. The data is grouped into 44 rows with 2 empty rows between them.

Sub testhaz()
    Dim lastrow As Long
    Dim total As Long
    Dim lastrow2 As Long
    Dim counter As Integer

    Range("e:k").ClearContents
    lastrow = Range("b1").End(xlDown).Row 
    total = WorksheetFunction.Sum(Range("b1:b" & lastrow))
    Range("k6").Value = total
    Range("j6").Value = Range("a2")

    For counter = 1 To 30
        lastrow2 = Range("k" & rows.Count).End(xlUp).Row
        total = WorksheetFunction.Sum(Range("b1:b" & lastrow + 1).Offset(Range("b" &_ 
        lastrow).End(xlDown).Row))
        Range("k" & lastrow2 + 1).Value = total
    Next counter
End Sub

Answer 1

Your code appears to differ from your written question. However, the code below should put you on the correct path to do the final adjustments yourself. All you have to do is to set the constants at the top to match your requirements.

Const FirstDataRow      As Long = 2      ' which is the first row containing data
Const GroupSize         As Long = 4      ' how many rows per group? you say 44. I tested 4
Const FirstColumn       As Long = 2      ' first column to sum up
Const LastColumn        As Long = 2      ' sum all columns from first to last
                                         ' this setting will just um one column

And here is the entire code. I left the Debug.Print in the middle which I used for testing. You may like to re-enable it for the same purpose.

Sub testhaz()
    ' 032

    Const FirstDataRow      As Long = 2             ' set to suit
    Const GroupSize         As Long = 4             ' set to suit
    Const FirstColumn       As Long = 2             ' first column to sum up
    Const LastColumn        As Long = 2             ' sum all columns from first to last

    Dim Rng                 As Range                ' working range
    Dim Rlast               As Long                 ' last used row in each column
    Dim Rstart              As Long                 ' first row of a group
    Dim Rend                As Long                 ' last row of a group
    Dim C                   As Long                 ' working column

    For C = FirstColumn To LastColumn
        Rstart = FirstDataRow
        Rlast = Cells(Rows.Count, C).End(xlUp).Row
        Do
            Rend = Application.Min(Rstart + GroupSize - 1, Rlast)
            Set Rng = Range(Cells(Rstart, C), Cells(Rend, C))
'            Debug.Print Rng.Address, Cells(Rend + 1, C).Address
            Cells(Rend + 1, C).Value = Application.Sum(Rng)

            Rstart = Rend + 3
            If Rstart > Rlast Then Exit Do
        Loop
    Next C
End Sub