Macro to Sum ALL the rows in a column - any number of rows

Originally Posted by davesexcel

Try this...

Sub SumStuff()

    Dim Rws As Long, Rng As Range


    Rws = Cells(Rows.Count, "A").End(xlUp).Row

    Set Rng = Range(Cells(2, 1), Cells(Rws, 1))

    Range("A1") = Application.WorksheetFunction.Sum(Rng)

End Sub

Works the first time I run it, but when more rows are added and I run it again, it sums the first number of rows 4478 and not the new number number of rows 4491.

try this.

Sub Test()

range("A1").value = worksheetfunction.sum(range("A:A"))

end sub

Originally Posted by lexusap

try this.

Sub Test()

range("A1").value = worksheetfunction.sum(range("A:A"))

end sub

This one works everytime I run it, big improvement, but it just plugs in the VALUE and is not a formula, so it doesn't change when rows are deleted.

how about this? Assuming all your data is in column A

sub Test1()

range("B1").formula = "=SUM(A:A)"

end sub

Result will change when you add or delete data

Originally Posted by davesexcel

Why not use an actual formula, instead of VBA that you originally asked for?

Formula:

=SUM(A2:INDEX(A:A,MATCH(9.999999E+307,A2:A65535)+1))

Place the formula in A1 and the sum will change automatically.

65536 is used for worksheets that only have 65536 rows, so if your workbook is in compatibility mode it will work.

SOLVED. This works really well, even if the data has blank rows or rows with text in them. Using the huge expotiential number is very clever, it is almost like your taking advantage of a known bug there can't be a number larger than that, so it goes to the next highest it can find.

Why would you want a VBA code for this in the first place, if you don't want to use it?

I'm working on a project to automate a time consuming manual process, where a data file gets emailed to us every month and then we prep the data (format, sort, sum, count, pivot table, paste special values) the same way every time and finally output it as a .txt file which gets uploaded into our accounting software as a journal entry.

I tried using the macro recorder but it doesn't type the keystrokes EXACTLY as I type them, it converts them to VBA code, which uses (A2:A478) instead of {CTRK}+{SHIFT}+{DOWN} and my macro only works if the number of rows of data doesn't change. So I'm trying to figure out a work around, like this one for =SUM() everytime I run into the (A2:A478) reference problem in my recorded VBA Code.

PS - And a big thanks for this, I'm originally from Canada (west coast), just temporarily working in the US.