# Thread: Calculate the sum of rows/columns/diagonal

1. Registered User
Join Date
Jun 2009
Posts
4

## Unanswered: Calculate the sum of rows/columns/diagonal

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...

2. Registered User
Join Date
Jun 2009
Posts
4
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```

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•