Sum in a macro

[tallguy6354]

Hi Guys,

I have a Macro and I would like sum one column after the last cell with a value in it. This cell has variable length with each file.

Is ther a way to include this function in my existing Macro?

[VBA Noob]

Maybe like

Please Login or Register  to view this content.

VBA Noob

[DonkeyOte]

Or if going the "other way" - ie Sum row in column after last cell

Please Login or Register  to view this content.

the above is based on row 1, alter as required (if required)

[broro183]

hi Tallguy,

The others have provided two working solutions for your question, however, I'll throw a question & some comments back for you to consider w.r.t. spreadsheet design...

Are you open to modifying your worksheet layout?
If so, I suggest...
- Inserting (if necessary) an extra row above the column headers on your spreadsheet & placing the equation in the cell on this row.
- Then using Freeze Panes to ensure that the headers & this equation cell are always visible.
- Changing from a Sum formula to a "=Subtotal(9,..." formula if you are using Autofilter on the sheet because the Subtotal version will only total the visible rows.

Advantages of the above aproach include:
-The total is always visible at the top.
-It allows for easy comparison of files because the total is always in the same cell (you can also "name the cell" to allow quicker formula entry that rely on the total.

Please Login or Register  to view this content.

hth
Rob

[shg]

- Inserting (if necessary) an extra row above the column headers on your spreadsheet & placing the equation in the cell on this row.
...
-The total is always visible at the top.

A very good suggestion, IMO. Excel 'prefers' that formulas reference data to the left and above, but this make sheets much easier to read, and in many cases, design.

[broro183]

Thanks Shg :-)

Yes, this situation is one of the few times I break the principle of "reference data to the left and above" but I justify it to myself by the fact that about a dozen is probably the absolute max' total cells that I would have at the top of a sheet (usually only 1 to 3 cells). I think the pro's of ease of use + future design/development probably outweigh the small (?) negative impact from this number of cells on the calculation dependency tree.

Rob