Excel Macro: How to total a column with changing lenghth

[mlbarry]

How do I have the macro automatically calculate a column where the row number length changes: (Below is my macro)


Range("R2").Select
Selection.End(xlDown).Select
Range("R2333").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-2331]C:R[-1]C)"
Range("R2334").Select

 

[WassimN]

Hi mlbarry,

I see you have recorded this code, now it is time to learn how to edit it, and make it work better.

So, change the lines:

Selection.End(xlDown).Select
Range("R2333").Select

to lLastRow= Selection.End(xlDown).Row

And now you have a variable called lLastRow that you can use to make sure you have the last row used.

MIND YOU: This will not work if you have blanks in between your data. The data should be contiguous.

You also do not need to select an object to work with it MOST of the time.

Best of luck leaning some more about VBA.

Oh another tip: You could get better code if at times you change the mode of recording by toggling the Use Relative References. This button is below the record button on the Developer ribbon.

 

[Fluff]

Try

Range("R" & Rows.Count).End(xlUp).Offset(1).FormulaR1C1 = "=SUM(R1C:R[-1]C)"

 

[mlbarry]

Thank you for your help.

 

[Fluff]

You're welcome