Using a Macro to Sum a Variable-Length Range (a Column)

[Chuckles123]

Dave,

Point well taken -- I agree; it is the mixing of R1C1 and A1 that i
the issue.

Thanks to you, I now know that SendKeys does not work when using VBE.
By the way, why is there one button for "Step Into" and one button fo
"Edit" when wanting to look at coding?

In my original macro, I inserted the formula =ROW(R[-5]C) in the cell
rows below the cell to contain the SUM formula (in order to capture th
end of the desired range). This cell was subsequently cleared in th
coding.

Thanks again for all your help,
Chuckles12

 

[Myrna Larson]

Edit just takes you to that procedure in the code window. Step Into begins to
execute the code, one line at a time.

 

[Dave Peterson]

So all you want is to sum from J3:J(one row above)?

Then how about:

With ActiveCell
.Formula = "=sum(J3:J" & .Row - 1 & ")"
.Value = .Value 'convert to values
End With

(I wasn't sure if you got your code working.)

Dave,

Point well taken -- I agree; it is the mixing of R1C1 and A1 that is
the issue.

Thanks to you, I now know that SendKeys does not work when using VBE.
By the way, why is there one button for "Step Into" and one button for
"Edit" when wanting to look at coding?

In my original macro, I inserted the formula =ROW(R[-5]C) in the cell 4
rows below the cell to contain the SUM formula (in order to capture the
end of the desired range). This cell was subsequently cleared in the
coding.

Thanks again for all your help,
Chuckles123