SUM for columns of variable length

[excelhack]

I am trying to total the values at the bottom of multiple columns of the same
length within a single worksheet. There are multiple worksheets within the
workbook, each having a different number of rows to be calculated in the
column. So, in the formula, the number of columns and the number of rows in
those columns must remain variable.

I have been unable to implement either the SUM function or FunctionR1C1
successfully using variables.

Does anyone know any good ways to calculate totals from columns of varying
lengths?

Thanks!

 

[Gary Keramidas]

maybe something like this. it will sum columns 1 through 14 (a-n)


Sub test()
Dim lrow As Long, x As Long
Dim ws As Worksheet
Set ws = Worksheets("sheet1")
lrow = ws.Cells(Rows.Count, "A").End(xlUp).Offset(1).Row
' sum the columns
For x = 1 To 14
lrow = ws.Cells(Rows.Count, x).End(xlUp).Offset(1).Row
If Application.Sum(Range(ws.Cells(2, x), ws.Cells(lrow, x))) >
0 Then
With ws.Cells(lrow, x)
.Formula = "=Sum(" & Range(ws.Cells(2, x),
ws.Cells(lrow - 1, x)).Address(0, 0) & ")"
.NumberFormat = "#,###"
End With
End If
Next
End Sub

 

[Dave Peterson]

I like to put my subtotals in row 1, headers in row 2 and data in rows 3 to
whatever.

Then I can use a formula like:

=sum(a3:a65536)
or
=subtotal(3,a3:a65536)

in row 1