insert Total in cells

[sal21]

In sheet have in column B a year in column C a month in column D and in column E a values.
Column B and C are already ordered by Year and Month similar:

B C D E
2006 1 1 2
2006 2 2 3
2006 3 1 1
...
2006 12 1 1
2007 1 1 2
2007 2 2 1
2007 3 1 1
...
2007 12 0 1

I need to insert a new line to the end of each sequence Year and Month a Total of value D and E
How to via vba code?

[rory]

Do you want totals for the year, or for year and month? (Your data only shows one entry per month)

[sal21]

Do you want totals for the year, or for year and month? (Your data only shows one entry per month)

see the attache file.
OLD sheet is wath i have now, and NEW sheet is wath i want:-) via vba code...

In effect loop into column year and month and when the sequence yar and month change add the line with a total.

[rory]

You could use subtotals:

Code: Select all

    Range("A1").CurrentRegion.Subtotal GroupBy:=2, Function:=xlSum, TotalList:=Array(4, 5, 6), _
        Replace:=True, PageBreaks:=False, SummaryBelowData:=True

[sal21]

You could use subtotals:

Code: Select all

    Range("A1").CurrentRegion.Subtotal GroupBy:=2, Function:=xlSum, TotalList:=Array(4, 5, 6), _
        Replace:=True, PageBreaks:=False, SummaryBelowData:=True

Dont understand how to use this function in a vba code

[rory]

That is VBA code. You just put it in whatever routine you are trying to do this in.

[sal21]

That is VBA code. You just put it in whatever routine you are trying to do this in.

Nice, but i dont want the left cursor slide ...

[rory]

How about this:

Code: Select all

   Dim vardata
    Range("A1").CurrentRegion.Subtotal GroupBy:=2, Function:=xlSum, TotalList:=Array(4, 5, 6), _
        Replace:=True, PageBreaks:=False, SummaryBelowData:=True
   vardata = Range("A1").CurrentRegion.Value
   Range("A1").CurrentRegion.RemoveSubtotal
   Range("A1").Resize(UBound(vardata, 1), UBound(vardata, 2)).Value = vardata