Sum of rows with the help of VBA

Hello Everyone, I want to create a total column at the end that sums each row. so instead of summing each column for Jan, Feb and Mar vertically, I want to sum each row for Jan, Feb, and Mar horiz...

ZanAutomateSumFunction.xlsm28 KB

Macros and VBA

HansVogelaar

Jul 01, 2021

Excel

Sub CreateRowSums()
    Dim wsh As Worksheet
    Dim lr As Long
    Dim lc As Long
    Application.ScreenUpdating = False
    For Each wsh In Worksheets
        lr = wsh.Cells.Find(What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
        lc = wsh.Cells.Find(What:="*", SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column
        wsh.Cells(2, lc).Resize(lr - 1).FormulaR1C1 = "=SUM(RC3:RC[-1])"
    Next wsh
    Application.ScreenUpdating = True
End Sub

Excel

Iron Contributor

Jul 01, 2021

Thank you for the reply.

But sir i want sum of each category with the help of VBA..

Please help..??

HansVogelaar

MVP

Jul 01, 2021

Excel

Sub CreateRowSums()
    Dim wsh As Worksheet
    Dim lr As Long
    Dim lc As Long
    Application.ScreenUpdating = False
    For Each wsh In Worksheets
        lr = wsh.Cells.Find(What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
        lc = wsh.Cells.Find(What:="*", SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column
        wsh.Cells(2, lc).Resize(lr - 1).FormulaR1C1 = "=SUM(RC3:RC[-1])"
    Next wsh
    Application.ScreenUpdating = True
End Sub

Excel
Iron Contributor
Jul 01, 2021
Thank you So Much sir.
It works😊😊

Forum Discussion

Sum of rows with the help of VBA

Resources