PDA

View Full Version : [SOLVED] Subtotal multiple blocks of data



ashleyuk1984
12-12-2013, 11:45 AM
Hi,
I've been stuck on this for a little while now, and just can't seem to get my head around it.

Basically, I have data that looks like this (changed some info due to confidentiality) - Pretend the RED values aren't there!!!.

http://www.ultraimg.com/images/KY2sQ.png

Basically, I want a macro that creates the RED values for me (subtotals each block separately), instead of doing it manually. I can't seem to work out a way of doing it?
A subtotal at the VERY bottom isn't required... Just the separate blocks of data :)
"RED" values aren't required either... It's just for illustration.


A simple understanding macro would be great then I can try to learn it :)
Thank you!!

Paul_Hossler
12-12-2013, 12:30 PM
Things are this are usually a little touchy depending on the data layout, so check it out




Option Explicit
Const iColToSum As Long = 5
Sub test()
Dim rArea As Range, rEnd As Range

Set rEnd = ActiveSheet.Cells(ActiveSheet.Rows.Count, iColToSum).End(xlUp)
Do While rEnd.Row > 1
Set rArea = rEnd.CurrentRegion
rEnd.Offset(1, 0).Value = Application.WorksheetFunction.Sum(rArea.Columns(iColToSum))
rEnd.Offset(1, 0).Interior.Color = vbRed ' marker
Set rEnd = rEnd.End(xlUp)
Set rEnd = rEnd.End(xlUp)
Loop
End Sub




Paul

ashleyuk1984
12-14-2013, 05:17 AM
Thank you very much. Works perfectly for my purpose. Rep given