VBA: Summing up a variable length row using ActiveCell.Formula

[mdmackillop]

You can skip the Autofill step

[VBA]Sub Formula2()
Dim nMonth As Long, nProd As Long

With Range("B4")
nMonth = Range(.Offset(0, 0), .End(xlToRight)).Columns.Count
nProd = Range(.Offset(0, 0), .End(xlDown)).Rows.Count
End With

Range("A" & nProd + 4).Value = "Total"
Range("A3").Offset(0, nMonth + 1).Value = "Total"

Range("B" & nProd + 4).Resize(1, nMonth).Formula = _
"=SUM(B4:B" & nProd + 3 & ")"
Cells(4, nMonth + 2).Resize(nProd).Formula = _
"=SUM(B4:" & Split(Cells(4, 1 + nMonth).Address(, 0), "$")(0) & "4)"
End Sub
[/VBA]