MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Addup column with macro


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

Hi,
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
Andonny



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


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

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