Excel: Create own subtotal

[RAxel]

Hello everyone. I wrote a script that takes all the information regarding payroll and formatted it the way the end user wants. Every individual on the sheet is identified by a code number, and are therefore grouped by their code number.

My question is this; after each group I created a subtotal for each group, the problem is that at the very end of the column I need to have a grand total that adds up the subtotals I made. Since this script will be used on many different excel files, I can't hard code where the subtotals will be which means I need a way to store where these subtotals are in a variable/array so I can create a grand total at the end. Here is the code I wrote to create the subtotals:

[Code}
Range("I3").Select
rowcounter = 3 'Creates counter of rows gone through in loop
StartCounter = 3 'Creates beginning counter of rows
Counter = 3
Do
Set currentcell = Worksheets("Consolidated_Quarters").Cells(Counter, 9)
If currentcell.Value <> ActiveCell.Offset(rowOffset:=1) Then
Rows(Counter).Select
Selection.Insert Shift:=xlDown 'Inserts new row
ActiveSheet.Cells(Counter, 10) = "=sum(" & "J" & StartCounter & ":" & "J" & rowcounter - 1 & ")"


Range("J" & Counter).Select 'Drag Formula to all cells
Selection.AutoFill Destination:=Range("J" & Counter, "AC" & Counter), Type:=xlFillDefault
Range("AA" & Counter).Select 'Delete Totaled Rate Cells

Range("A" & Counter, "AD" & Counter).Select 'Select Entire Row to Color Gray
With Selection.Interior
.ColorIndex = 15
.Pattern = xlSolid
End With
Selection.Font.Bold = True
Range("I" & Counter + 1).Select
Counter = Counter + 1
StartCounter = Counter
rowcounter = Counter + 1
Else
rowcounter = rowcounter + 1
End If
Counter = Counter + 1
Loop Until currentcell.Value = ""
[/Code]

Basically it starts at the top of the column, compares the code number in the cell to the code number below it, and if they are different, it inserts a new row and this is where the subtotals are made. I figure if I could make an array that stores these cell names (ie. J7:J35:J79...) I could create the grand total sum formula at the end. I hope this makes some kind of sense. Thanks in advance!

 

[RAxel]

crud, hit the wrong bracket. In case you want to see it in the pretty background, here it is again

Range("I3").Select
    rowcounter = 3                                                      'Creates counter of rows gone through in loop
    StartCounter = 3                                                    'Creates beginning counter of rows
    Counter = 3
    Do
            Set currentcell = Worksheets("Consolidated_Quarters").Cells(Counter, 9)
            If currentcell.Value <> ActiveCell.Offset(rowOffset:=1) Then
                Rows(Counter).Select
                Selection.Insert Shift:=xlDown                          'Inserts new row
                ActiveSheet.Cells(Counter, 10) = "=sum(" & "J" & StartCounter & ":" & "J" & rowcounter - 1 & ")"
                
                
                Range("J" & Counter).Select                             'Drag Formula to all cells
                Selection.AutoFill Destination:=Range("J" & Counter, "AC" & Counter), Type:=xlFillDefault
                    Range("AA" & Counter).Select                        'Delete Totaled Rate Cells
                
                Range("A" & Counter, "AD" & Counter).Select             'Select Entire Row to Color Gray
                With Selection.Interior
                    .ColorIndex = 15
                    .Pattern = xlSolid
                End With
                Selection.Font.Bold = True
                Range("I" & Counter + 1).Select
                Counter = Counter + 1
                StartCounter = Counter
                rowcounter = Counter + 1
            Else
                rowcounter = rowcounter + 1
            End If
            Counter = Counter + 1
    Loop Until currentcell.Value = ""

 

[SkipVought]

Hi,

Have you tried using the Excel built-in Subtotal Feature in Data/Subtotals...

Skip,

_{[red]Be Advised![/red]
The band of elderly oriental musicians, known as Ground Cover, is, in reality...
Asian Jasmine!}

 

[RAxel]

I suppose that would work, but I just hate the subtotal look on the side But hey, if I have to I'll use it. I'm just curious though, can you create an array that continually updates itself to store the cell range (ie. J36, J79, J101, etc.) NOT the cell value? Thereby being used in a sum formula at the end.

Unfortunately, I can get the code to add up each cell (by totaling the cells that are bold) and print out the grand total at the bottom, but then I can't drag the formula to the other columns because it isn't the built in sum formula. Thanks.

 

[GlennUK]

If you use the Subtotal feature of Excel the Grand Total can be done with the SUBTOTAL function very easily.

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[demopro]

I am by no means an expert on this subject but why can't you carry the values in a temp variable that you can then add on to the bottom of the document. Every time this function fires it will add the sum value to a variable such as

var = var + newsumamount

Just a thought.

Demopro