SOLVED

Autosum

Occasional Contributor

does anyone know the VBA code for autosum. for instance, when pressing the autosum button in the formulas tab, the whole column gets summed up, regardless of what row the cell is on.

1 Reply
best response confirmed by Josh_Waldner (Occasional Contributor)
Solution

@Josh_Waldner in its simplest form, it would be something like below :-

 

 

Sub MyAutoSum()

    Application.CutCopyMode = False
    
    Dim rngSumRange As Range
    Set rngSumRange = Range(ActiveCell.Offset(-1, 0), ActiveCell.Offset(-1, 0).End(xlUp))
    
    ActiveCell.Formula = "=SUM(" & rngSumRange.Address & ")"

End Sub

 

 

Though it is not as intelligent as autosum button

 

Example, below column, autosum button sums as 123, but above simple macro sums as zero. I am not sure of what all behaviour and checks are there in autosum button, but if one can list, then perhaps with more sophisticated macro, one can emulate it in VBA.

 

-123
abc
123