autosum in VBA

[Guest]

If I am writing an Excel VBA macro with relative references turned on, how do
i write code that creates a Sum formula that adds up a different number of
rows each time?

When I record the macro it seems to specify the exact number of rows it is
using in the formula. But I need the code to create the formula based on the
number of cells selected, not the same number of cells every time.

How do I do this?

Thanks if you have an answer!!

Mark

 

[Bernie Deitrick]

Mark,

Try something along the lines of:

Sub MyAutoSum()
With ActiveCell
.Formula = "=SUM(" & _
Range(.offset(-1, 0), _
.End(xlUp).End(xlUp)).Address(False, False) & ")"
End With
End Sub

Select a cell at the bottom of an arbitrarily long list of numbers, and run
it.

HTH,
Bernie
MS Excel MVP