Auto Sum

[e17nel]

Hello again.

My current problem is something I thought would be easy because I thought VBA would have an Auto Sum function, however I have not been able to find one. I'm using Excel 2007.

I have a template that has a summary page. The summary page has seven sections, each section length (in rows) depends on how many sheets the user adds. (i.e. the user adds 7 worksheets, the section will be seven rows). Each row has data grabbed from the worksheet. I want the row, directly below the last row to auto sum the data for that section. See below for example:

Col A Col B
Row 1: 5 16
Row 2: 7 18
Row 3: 8 9
Row 4: 9 5
Row 5: 4 13
Row 6: 16 2
Row 7: (SUM) (SUM)
Row 8: (*Blank*) etc...
Row 9: (*Blank*)
Row 10: (*Blank*)
Row 11: 6
Row 12: 7
Row 13: 25
Row 14: 36
Row 15: 95
Row 16: (SUM)
Row 17: (*Blank*)
Row 18: (*Blank*)
Row 19: (*Blank*)
Row 20: 15
etc...

The columns will always stay constant but because of the sections I can't do range("A9999999".End(xlup)).

I have tried

ActiveAell.Offset(2, 0) 'to get to the desired cell for the sum formula
ActiveCell.FormulaR1C1 = "=SUM(" & ActiveCell.Offset(-1, 0).Address & ":" & ActiveCell.Offset(-1, 0).End(xlUp).Address & ")"

Thanks for any help that can be provided.

 

[cmyers]

Sub auto_sum()
Application.ScreenUpdating = False

Dim s, e, LastRow As Long

LastRow = Range("A65532").End(xlUp).Row
e = 1
s = 1

Do Until e > LastRow
    If e = 1 Or Range("A" & e).HasFormula Then GoTo NextLoop
        
    If Range("A" & e).Value > 0 And Range("A" & e - 1).Value = 0 Then
        s = e
    End If
    
    If Range("A" & e).Value > 0 And Range("A" & e + 1).Value = 0 Then
       Range("A" & e + 1).Value = "=SUM(A$" & s & ":A$" & e & ")"
       Range("B" & e + 1).Value = "=SUM(B$" & s & ":B$" & e & ")"
    End If
    
NextLoop:
e = e + 1
Loop

Application.ScreenUpdating = True

End Sub

Hope this helps!

 

[e17nel]

This works great! I forgot to add one part. Not all sections have the same number of columns. So for example, section 1 has six columns. Sections two through five have four columns and sections six and seven have 10 columns. Is there a way to do this. Worse comes to worse, I can just delete the extra sums after the code runs. Thank you!

Sub auto_sum()
Application.ScreenUpdating = False
 
Dim s, e, LastRow As Long
 
LastRow = Range("A65532").End(xlUp).Row
e = 1
s = 1
 
Do Until e > LastRow
    If e = 1 Or Range("A" & e).HasFormula Then GoTo NextLoop
 
    If Range("A" & e).Value > 0 And Range("A" & e - 1).Value = 0 Then
        s = e
    End If
 
    If Range("A" & e).Value > 0 And Range("A" & e + 1).Value = 0 Then
       Range("A" & e + 1).Value = "=SUM(A$" & s & ":A$" & e & ")"
       Range("B" & e + 1).Value = "=SUM(B$" & s & ":B$" & e & ")"
    End If
 
NextLoop:
e = e + 1
Loop
 
Application.ScreenUpdating = True
 
End Sub

Hope this helps!