Calculate the sum of rows/columns/diagonal

Manhamanha · #1 (**permalink**) 06-02-09, 08:15

I have a table with changing length of the rows and columns. I need to calculate the sums of the rows/columns/diagonal and write it in the next blank cell. I managed to do it for the columns but have no success with the other. Here is what I have:
Sub AutoSumCol()
Dim lngColumn As Long
Dim lngLastRow As Long
For lngColumn = 1 To Range("A1").End(xlToRight).Column
lngLastRow = Cells(1, lngColumn).End(xlDown).Row
Cells(lngLastRow + 1, lngColumn).Formula = "=Sum(" & Cells(1, lngColumn).Address & ":" & _
Cells(lngLastRow, lngColumn).Address & ")"
Next lngColumn
End Sub
Could you please help me with the other two sums...

Manhamanha · #2 (**permalink**) 06-02-09, 09:09

I got some help with this, here is the solution in case anybody else needs it:

Code:

Sub AutoSum()
    Dim r       As Range
    Dim nRow    As Long
    Dim nCol    As Long

    Set r = Range("A1").CurrentRegion
    nRow = r.Rows.Count
    nCol = r.Columns.Count

    r.Columns(nCol + 1).Formula = "=sum(" & r.Rows(1).Address(False, False) & ")"
    r.Rows(nRow + 1).Formula = "=sum(" & r.Columns(1).Address(False, False) & ")"
    r(nRow + 1, nCol + 1).Formula = "=sumproduct(" & r.Address & _
                                    " * (row(" & r.Address & _
                                    ") = column(" & r.Address & ") ) )"
End Sub

Internet Services

Web Development

Digital Marketing

Consumer Tech