SUM for columns of variable length

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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!
 
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
 
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
 
Back
Top