Is there a better way to do this?

[G Lam]

Hi,
is there a better way to do the following:

Range("B2").Formula = "=Sum(" + Range(Range("D6").Offset(0, 2),
Range("D6").End(xlDown).Offset(0, 2)).Address + ")"

Thank you.
GL

 

[Bob Phillips]

Couple of things. The offset is superfluous. If you have a hard-coded cell
(D6) that you offset (by 2 columns), you might as well use that cell (F6).

You might also want to check that there is data after F6, otherwise you
could go all the way down the column

Dim cRow As Long

cRow = Range("D" & Rows.Count).End(xlUp).Row
If cRow < 6 Then cRow = 6
Range("B2").Formula = "=Sum(F6:F" & cRow & ")"


--

HTH

RP
(remove nothere from the email address if mailing direct)