Dynamic AutoSum

[Mike2]

Hello,

I am looking to do a Dynamic AutoSum function in VBA.

Here is my code:

Rows(1).Find(what:="Qty $", lookat:=xlWhole).Select
LstRow = Cells(Rows.Count, ActiveCell.Column).End(xlUp).Row + 1
Cells(LstRow, ActiveCell.Column).Formula = "=SUM(ActiveCell:ActiveCell.Column" & LstRow - 1 & ")"

The execution gave me a #NAME ? error at the end of the row.

But let say "Qty $" is in Column B, and when I enter the formula as
"=SUM(B1:B" & LstRow - 1 & ")" in replacement to the one above, then it works find.

Is there a way to not specify the Range and use Dynamic Range instead?

Thank you for your expert advise in advance...

 

[Mike2]

Thank you @Michael M and @Norie for your coding suggestion.

Both ways work fine in my application. Thanks a bunch !!

 

[Mike2]

@Norie, Thank you for your help...the code works great !!