VBA equivalent of Autosum

Question

Is there a VBA equivalent of Alt+= (Autosum) to sum a continuous block of numbers?

I have tried a using SendKeys "%=" but this only gives SUM() without the reference.

Edit: I have rows of data followed by 2 blank rows, beneath each set of data I want to add a sum.

Amount Description  
500 Text1  
500 Text2  
SUM HERE OF AMOUNT  

600 Text1  
600 Text2  
600 text3  
SUM HERE OF AMOUNT  

Answer 1

You may use these VBA Macro as Standard Module:

Sub AutoSumRange()
 Dim lCell As Range
 Set lCell = Range("A1").End(xlDown).Offset(1, 0) 

 With Sheet1
 lCell.Formula = WorksheetFunction.Sum(Range(Range("A1"), Range("A1").End(xlDown)))
 End With
End Sub

N.B.

• This Macro will put SUM in the Last Row/Cell in Column A(suppose you have value in A2:A5, then in A6 and so on) and adjust every new value as and when inserted.
• Cell A1 has Header value.

---

Edited:

Adding this VBA code since reader's observation suggests to apply the SUM Formula in Last Cell & Macro should work like Keyboard Shortcut.

Sub Test()
    Dim Rng As Range
    Dim c As Range
    Set Rng = Range("N1:N" & Range("N1").End(xlDown).Row)
    Set c = Range("N1").End(xlDown).Offset(1, 0)
    c.Formula = "=SUM(" & Rng.Address(False, False) & ")"
End Sub

---

N.B.

• Create Keyboard Shortcut to RUN the Macro.

• In above codes, Sheet Name & Cell references are editable.

Answer 2

Here is a Sub which works with your test data:

Sub AutoSumColumn()
    Dim rngToSum As Range
    Dim TopCell As Range
    Dim BottomCell As Range

    Set rngToSum = Intersect(Selection.EntireColumn, ActiveSheet.UsedRange)
    Set TopCell = rngToSum.Cells(1, 1)
    If TopCell.Value = vbNullString Then
        Set TopCell = TopCell.End(xlDown)
    End If
    Do
        Set BottomCell = TopCell.End(xlDown)
        BottomCell.Offset(1, 0).Formula = "=sum(" & Range(TopCell, BottomCell).Address & ")"
        Set TopCell = BottomCell.Offset(1, 0).End(xlDown)
    Loop Until Intersect(rngToSum, TopCell) Is Nothing
End Sub

How it works:

• select any cell in the column where you want to add autosum
• start the macro

The macro then creates sum formula for each continuous range. It'll include reference to text fields too, however it won't affect result.

Sheet after running the macro: