Results 1 to 3 of 3
  1. #1
    2 Star Lounger
    Join Date
    Feb 2015
    Posts
    128
    Thanks
    19
    Thanked 1 Time in 1 Post

    Post Vba code help to sum dynamic range of columns

    Hi Experts

    The below code works fine when I have to sum the entire data dynamically. I do not want to apply the sum formula on column A and want to apply it on rest of the columns. I tried tweaking it, but failed to achieve the desire result.

    Code:
    With Sheets("sheet2").Range("A1").CurrentRegion
            With .Offset(.Rows.Count).Resize(1)
                .Formula = _
                "=sum(r2c:r[-1]c)"
                .Interior.ColorIndex = 46
            End With
        End With

  2. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Jaggi,

    This should do the trick:
    Code:
    Option Explicit
    
    Sub AddTotals()
    
       Dim lTotalRow As Long
       Dim lLastCol  As Long
       Dim lCntr     As Long
       
       lTotalRow = [a1].CurrentRegion.Rows.Count + 1
       lLastCol = [a1].CurrentRegion.Columns.Count
       
       For lCntr = 1 To lLastCol
          Cells(lTotalRow, lCntr).FormulaR1C1 = "=Sum(R1C" & lCntr & _
               ":R" & lTotalRow - 1 & "C" & lCntr & ")"
       Next lCntr
       
    End Sub
    HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  3. The Following User Says Thank You to RetiredGeek For This Useful Post:

    Jaggi (2015-07-02)

  4. #3
    2 Star Lounger
    Join Date
    Feb 2015
    Posts
    128
    Thanks
    19
    Thanked 1 Time in 1 Post
    Hi RG

    Thanks for the code. It fits into my need verywell!

    Regards,
    JD

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •