0

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  
  • 1
    did you try recording a macro and looking at the results? – gns100 Dec 3 at 1:51
  • What do you want to autosum? Selection? Range above selection? Why do you need to do it in VBA? – Máté Juhász Dec 3 at 5:07
  • No such equivalent as standard function/procedure. You must create your own procedure, where you must check if CurrentCell.Offset(-1,0) is empty (i.e. does you must to sum horizontal or vertical range). – Akina Dec 3 at 6:06
0

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.

enter image description here

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.

enter image description here

  • In above codes, Sheet Name & Cell references are editable.
  • "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." - that's not true worksheetfunction returns the sum as amount, not inserting formula into cell, it won't update automatically. docs.microsoft.com/en-us/office/vba/api/… – Máté Juhász Dec 3 at 5:23
  • @MátéJuhász, I don't think that OP is in need of Auto updation,,, but this works successfully when new value inserted (insert row) or modified & RUN it,,, ! – Rajesh S Dec 3 at 8:44
  • @MátéJuhász,, I've updated the post ,, new Macro inserts formula as well RUN through KB Shortcut also. – Rajesh S Dec 3 at 9:30
0

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:

enter image description here

  • ,, if I'm not wrong OP has never demanded for SUBTOTALs,,, but the concern was how to update Column Total,,, !! – Rajesh S Dec 4 at 4:46
  • Also,, whenever the location changes it doesn't works suppose I've data from A15:A18 and I've selected Cell A19 ,, this macro puts formula =SUM(A1:A18),, which should =SUM(A15:A18) ! – Rajesh S Dec 4 at 4:58
  • @RajeshS This code works on the sample data provided by OP. – Máté Juhász Dec 4 at 5:20
  • @ Máté Juhász ,, when I've had posted my answer Yday,, Op hasn't any Sample data,, !! And no link is available with post also. – Rajesh S Dec 4 at 5:26
  • @MátéJuhász This almost works but for one lines (3rd example) it breaks down it keeps going to the next lot and then sums both. – Naz Dec 12 at 19:41

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.