Stuck on VBA code to sum xlup range

[Redford Kenny]

Hello,

Trying to put a SUM formula a couple of rows below my array but I am having no luck.

Can you look at the code and advise please.

Range("J17").End(xlDown).Offset(4, 0).Select
    ActiveCell.Offset(-4, 0).Select
   
    Dim x As Range
   
    Set x = Range(Selection, Selection.End(xlUp)).Select

   
    Range("J17").End(xlDown).Offset(4, 0).Select
    ActiveCell.Formula = "=SUM(" & x.Address & ")"

Many thanks,
Red

 

[Joe4]

When setting a range variable (object), you do NOT use "SELECT", i.e.
this:

Set x = Range(Selection, Selection.End(xlUp)).Select

should just be this:

Set x = Range(Selection, Selection.End(xlUp))

(assuming your logic is correct, we cannot tell since we do not know what your data looks like).

Also, most anytime you have one line of code end in "Select", and the next begins with "Selection" or "ActiveCell", you can combine those two lines together.
Not only does it make your code shorter, but "Selections" are usually unnecessary and slow your code down (you usually do not need to "Select" ranges to work with them in VBA).

So these lines:

Range("J17").End(xlDown).Offset(4, 0).Select
ActiveCell.Formula = "=SUM(" & x.Address & ")"

could simply be reduced to:

Range("J17").End(xlDown).Offset(4, 0).Formula = "=SUM(" & x.Address & ")"

 

[Fluff]

In future please you code tags rather than quotes when posting code How to Post Your VBA Code

Replace all your code with

Range("J17").End(xlDown).Offset(4, 0).FormulaR1C1 = "=sum(r17c:r[-4]c)"

 

[Redford Kenny]

Hello,

They both worked perfectly, thanks so much. My VBA is pedestrian at best so I appreciate the tips on the range and the R1C1 sum (very interesting how it picks up the entire cell after!).

 

[Fluff]

Glad we could help & thanks for the feedback.