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

[RM-Vallejo]

I need a macro (at top of the column), to SUM all of the rows below.

--------------------------------------------------------------------------------------------

My keystrokes are:

Please Login or Register  to view this content.

--------------------------------------------------------------------------------------------

Excel 2010 converts that to:

Please Login or Register  to view this content.

--------------------------------------------------------------------------------------------

How do I get VBA to sum ALL rows and not just (A2:A4478) ?

[davesexcel]

Try this...

Please Login or Register  to view this content.

[lexusap]

try this.

Sub Test()

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

end sub

[davesexcel]

try this.

Sub Test()

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

end sub

lexusap, please use code tags when supplying VBA code, BTW, you can't use A1 in the sum formula, you would get a circular reference.

[RM-Vallejo]

Try this...

Please Login or Register  to view this content.

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.

[RM-Vallejo]

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.

[lexusap]

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

Please Login or Register  to view this content.

Result will change when you add or delete data

[davesexcel]

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

Please Login or Register  to view this content.

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.

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

[RM-Vallejo]

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

Please Login or Register  to view this content.

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.

[davesexcel]

With the formula in A1.
Record Macro
Select A1
Select the Formula Bar and hit Enter
Stop recording

This is what your recorded macro looks like

Please Login or Register  to view this content.

Now you can clean it up

Please Login or Register  to view this content.