Sum a dynamic range with VBA sum which should be present even after VBA execution

[krshnn]

Range("B2").formula = "=sum(b4:b300)".
The issue being here is b300 could be b400 or b100.
I want basically a sum formula which would stay even after VBA execution.
When I use worksheet function sum, it correctly computes but once the VBA is executed, only value is shown with formula gone.

So in nutshell, I want to sum a dynamic range. After summing the dynamic range and completion of VBA execution, sum formula should be present.

Disclaimer : I don't want to define name for the sum range and sum it. Naming sum range VBA method is not preferred

 

[Flaiban]

Will that do?

Sub SumColumn_B()

    Dim FinalRow_B As Long
    Dim WS As Worksheet
    
    Set WS = ActiveSheet
    FinalRow_B = WS.Cells(Rows.Count, 2).End(xlUp).Row + 1
    Range("B2").Formula = "=SUM(B4:B" & FinalRow_B - 1 & ")"

End Sub

 

[krshnn]

Thanks for your assistance. It works.