Results 1 to 2 of 2
  1. #1
    Join Date
    Jun 2009
    Posts
    4

    Unanswered: Calculate the sum of rows/columns/diagonal

    I have a table with changing length of the rows and columns. I need to calculate the sums of the rows/columns/diagonal and write it in the next blank cell. I managed to do it for the columns but have no success with the other. Here is what I have:
    Sub AutoSumCol()
    Dim lngColumn As Long
    Dim lngLastRow As Long
    For lngColumn = 1 To Range("A1").End(xlToRight).Column
    lngLastRow = Cells(1, lngColumn).End(xlDown).Row
    Cells(lngLastRow + 1, lngColumn).Formula = "=Sum(" & Cells(1, lngColumn).Address & ":" & _
    Cells(lngLastRow, lngColumn).Address & ")"
    Next lngColumn
    End Sub
    Could you please help me with the other two sums...

  2. #2
    Join Date
    Jun 2009
    Posts
    4
    I got some help with this, here is the solution in case anybody else needs it:
    Code:
    Sub AutoSum()
        Dim r       As Range
        Dim nRow    As Long
        Dim nCol    As Long
    
        Set r = Range("A1").CurrentRegion
        nRow = r.Rows.Count
        nCol = r.Columns.Count
    
        r.Columns(nCol + 1).Formula = "=sum(" & r.Rows(1).Address(False, False) & ")"
        r.Rows(nRow + 1).Formula = "=sum(" & r.Columns(1).Address(False, False) & ")"
        r(nRow + 1, nCol + 1).Formula = "=sumproduct(" & r.Address & _
                                        " * (row(" & r.Address & _
                                        ") = column(" & r.Address & ") ) )"
    End Sub

Posting Permissions

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