Calculate the sum of rows using macro [Solved/Closed]

Manhamanha - Jun 2, 2009 at 06:56 AM - Latest reply:  Manhamanha
- Jun 3, 2009 at 05:57 AM
Hello,
I need some help with the folowing problem. I have a table with changing length of rows and columns. I need to calculate the sums of the rows and columns and write it in the next blank cell. I managed to write a working macro for the columns but I have a problem with the rows. Could you please help? Here are the two codes...

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

Sub AutoSumRow()
Dim lngRow As Long
Dim lngLastColumn As Long
For lngRow = 1 To Range("A1").End(xlToRight).Row
lngLastColumn = Cells(1, lngRow).End(xlDown).Column
Cells(lngLastColumn + 1, lngRow).Formula = "=Sum(" & Cells(1, lngRow).Address & ":" & _
Cells(lngLastColumn, lngRow).Address & ")"
Next lngRow
End Sub

Thanks :)
See more 

2 replies

Best answer
Excelguru 261 Posts Saturday April 11, 2009Registration date June 21, 2011 Last seen - Jun 3, 2009 at 03:58 AM
2
Thank you
Hi

The problem seems to be in the line Cells(lngLastColumn + 1, lngRow).Formula = "=Sum(" & Cells(1, lngRow).Address & ":" & _
Cells(lngLastColumn, lngRow).Address & ")"
I think , here you have to use Cells(lngRow,lngLastColumn + 1).

My suggestion is to use activesheet.usedrange.rows.count instead of Range.End(xlToRight) and the like statements.

Please revert back if you have any qurties

Thank you, Excelguru 2

Something to say? Add comment

CCM has helped 1673 users this month

I managed to deal with both problems with one method. Thanks for the help :)