Forum Discussion

Josh_Waldner's avatar
Josh_Waldner
Brass Contributor
Nov 22, 2021
Solved

Autosum

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.

Macros and VBA
  • amit_bhola's avatar
    amit_bhola
    Nov 30, 2021

    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

     

     

     

1 Reply

  • amit_bhola's avatar
    amit_bhola
    Iron Contributor
    Nov 30, 2021

    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

     

     

     

Resources