MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Addup column with macro

Posted by Andonny on November 03, 2001 2:12 PM

I have this macro but I am experiencing a problem. It works fine if all rows
have a number in it. Lets say I have numbers from A1 to A10 except there is
a blank cell in A8. If I run the macro it will only add up to A8 not to A10.
It seems when it is trying to find the End(xlDown) it does not realize that
it is not the last row. What I am really looking for is a macro which can
add up a column from A3 to last row even if there are blank cells in A1, A2
and A8 and then placing the total at the bottom of the column.

Sub AddColumn()
LastRow = Range("A1").End(xlDown).Offset(1, 0).Row
sumrangenumber = LastRow - 2
Range("A" & LastRow + 1 & ":A" & LastRow + 1).FormulaR1C1 = "=SUM(R[" &
sumrangenumber * -1 & "]C:R[-1]C)"
End Sub

Thanks a lot

Posted by bob umlas on November 03, 2001 4:48 PM

LastRow = Range("A1").End(xlDown).Offset(1, 0).Row

LastRow = Range("A65536").End(xlUp).Offset(1, 0).Row