VBA to sum based on certain criteria

[markh1182]

Hi, more help required I'm afraid.

In cells A1 to A4 I might have values of 100.00. In cell A6 might be 200.00 and cells A9 to A12 I might have 300.00.

What I want is in cell B5 it will put an X so that in cell C5 it can sum the amounts A1 to A4. In cell B7 it will put an X and sum in C7 and B13 will be an X and C13 will be a sum of A9 to A12.

Now I am sure this is possible but not sure how to about it. In effect every gap it will sum the previous x rows.

Thank you, Mark

 

[Krishnakumar]

Hi,

Try,

Sub SumBlocks()
Dim SumRng  As Variant
Dim aRng    As Range

For Each aRng In Columns(1).SpecialCells(xlCellTypeConstants, 1).Areas
    SumRng = aRng.Address(0, 0)
    Cells(aRng.Row + aRng.Rows.Count, "A") = "=SUM(" & SumRng & ")"
Next
End Sub

HTH

 

[markh1182]

Thanks, looks like this works