none
Sum dynamically RRS feed

  • Question

  • Hello,

    I would like some help in how Can I sum the first values of columns to last cell with values. 

    I get a code in a group but the behave of it is just for the selected rows, in other words, I need to select and after click sum to sum the colomns that I want.

    Follow the code:

    Sub TotalColumns()
        
        Dim rngRange As Range
        Dim rngCell As Range
        
        If TypeName(Selection) = "Range" Then
        
        Set rngRange = Selection
            For Each rngCell In rngRange.Cells
                Cells(Cells(Rows.Count, rngCell.Column).End(xlUp).Row + 1, rngCell.Column).Formula = "=Sum(" & _
                rngCell.Address & ":" & Cells(Cells(Rows.Count, rngCell.Column).End(xlUp).Row, rngCell.Column).Address & ")"
                
                Cells(Cells(Rows.Count, rngCell.Column).End(xlUp).Row, rngCell.Column).Font.Bold = True
            Next rngCell
        End If
        
    End Sub

    Thanks in Advance,

    Michael

    Thursday, October 3, 2013 2:49 PM

Answers

  • Hi Michael,

    Base on my understanding, you want to sum the first values of the range selected.

    The resoult would return 11, if I selected the range like figure below:

    If I understood correctly, you can refer code below:

    Sub sumFirstValueOfColumns()
    Dim selectRange As Range
    Set selectRange = Selection
    Dim beginRow As Integer
    beginRow = selectRange.Row
    Dim resoult As Double
    resoult = 0
    For i = 1 To selectRange.Columns.Count
        If Cells(beginRow, selectRange.Columns(i).column) = "" Then
            resoult = resoult + Cells(beginRow, selectRange.Columns(i).column).End(xlDown)
        Else
            resoult = resoult + Cells(beginRow, selectRange.Columns(i).column)
        End If
    Next i
    MsgBox resoult
    End Sub

    Best regards

    Fei


    <THE CONTENT IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND, WHETHER EXPRESS OR IMPLIED>
    Thanks
    MSDN Community Support

    Please remember to "Mark as Answer" the responses that resolved your issue. It is a common way to recognize those who have helped you, and makes it easier for other visitors to find the resolution later.

    Friday, October 4, 2013 10:02 AM
    Moderator