Consulting

Results 1 to 6 of 6

Thread: Sum Cell Values Until Blank Cells With VBA

  1. #1

    Unhappy Sum Cell Values Until Blank Cells With VBA

    I found this code on the internet. How do I get the sum value to appear in the empty cell above the cells containing consecutive values. I'm thinking of running the code from the last row. Thank for your help


    https://www.extendoffice.com/documen...til-blank.html

    Sub InsertTotals()
    'Updateby Extendoffice
        Dim xRg As Range
        Dim i, j, StartRow, StartCol As Integer
        Dim xTxt As String
        On Error Resume Next
        xTxt = ActiveWindow.RangeSelection.AddressLocal
        Set xRg = Application.InputBox("please select the cells:", "Kutools for Excel", xTxt, , , , , 8)
        If xRg Is Nothing Then Exit Sub
        StartRow = xRg.Row
        StartCol = xRg.Column
        For i = StartCol To xRg.Columns.Count + StartCol - 1
            For j = xRg.Row To xRg.Rows.Count + StartRow - 1
                If Cells(j, i) = "" Then
                    Cells(j, i).Formula = "=SUM(" & Cells(StartRow, i).Address & ":" & Cells(j - 1, i).Address & ")"
                    StartRow = j + 1
                End If
            Next
            StartRow = xRg.Row
        Next
    End Sub
    Last edited by Aussiebear; 10-01-2021 at 12:39 PM. Reason: Added code tags to supplied code

  2. #2
    VBAX Guru mancubus's Avatar
    Joined
    Dec 2010
    Location
    "Where I lay my head is home" :D
    Posts
    2,644
    welcome to the forum

    try this:

    Sub vbax_69213_Sum_Cell_Values_Until_Blank_Cells()
    
        Dim LastRow As Long, CounterRow As Long, BottomRow As Long
        
        LastRow = Range("A" & Rows.Count).End(xlUp).Row 'assuming numbers to sum and blank sum cells are in col A
        BottomRow = LastRow
        
        For CounterRow = LastRow To 3 Step -1 'assuming first blank sum cell is A3
            If Range("A" & CounterRow) = "" Then
                Range("A" & CounterRow) = Application.Sum(Range("A" & CounterRow + 1, "A" & BottomRow))
                BottomRow = CounterRow - 1
            End If
        Next CounterRow
        
    End Sub
    PLS DO NOT PM; OPEN A THREAD INSTEAD!!!

    1) Posting Code
    [CODE]PasteYourCodeHere[/CODE]
    (or paste your code, select it, click # button)

    2) Uploading File(s)
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) (multiple files can be selected while holding Ctrl key) / Upload Files / Done
    Replace company specific / sensitive / confidential data. Include so many rows and sheets etc in the uploaded workbook to enable the helpers visualize the data and table structure. Helpers do not need the entire workbook.

    3) Testing the Codes
    always back up your files before testing the codes.

    4) Marking the Thread as Solved
    from Thread Tools (on the top right corner, above the first message)

  3. #3
    VBAX Guru mancubus's Avatar
    Joined
    Dec 2010
    Location
    "Where I lay my head is home" :D
    Posts
    2,644
    sum cells are formatted:

    Sub vbax_69213_sum_cells_until_blank_cells_above()
    
        Dim LastRow As Long, CounterRow As Long, BottomRow As Long
        
        LastRow = Range("A" & Rows.Count).End(xlUp).Row 'assuming numbers to sum and blank sum cells are in col A
        BottomRow = LastRow
        
        For CounterRow = LastRow To 3 Step -1 'assuming first blank sum cell is A3 and first number cell is A4
            If Range("A" & CounterRow) = "" Then
                Range("A" & CounterRow) = Application.Sum(Range("A" & CounterRow + 1, "A" & BottomRow))
                Range("A" & CounterRow).Font.Bold = True
                Range("A" & CounterRow).Interior.Color = vbYellow
                BottomRow = CounterRow - 1
            End If
        Next CounterRow
        
    End Sub
    PLS DO NOT PM; OPEN A THREAD INSTEAD!!!

    1) Posting Code
    [CODE]PasteYourCodeHere[/CODE]
    (or paste your code, select it, click # button)

    2) Uploading File(s)
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) (multiple files can be selected while holding Ctrl key) / Upload Files / Done
    Replace company specific / sensitive / confidential data. Include so many rows and sheets etc in the uploaded workbook to enable the helpers visualize the data and table structure. Helpers do not need the entire workbook.

    3) Testing the Codes
    always back up your files before testing the codes.

    4) Marking the Thread as Solved
    from Thread Tools (on the top right corner, above the first message)

  4. #4
    VBAX Guru mancubus's Avatar
    Joined
    Dec 2010
    Location
    "Where I lay my head is home" :D
    Posts
    2,644
    in case you need to insert sums below numbers use;

    Sub vbax_69213_sum_cells_until_blank_cells_below()
    
        Dim LastRow As Long, CounterRow As Long, TopRow As Long
        
        LastRow = Range("A" & Rows.Count).End(xlUp).Row 'assuming numbers to sum and blank sum cells are in col A
        TopRow = 3
        
        For CounterRow = TopRow To LastRow + 1 'assuming first number cell is A3
            If Range("A" & CounterRow) = "" Then
                Range("A" & CounterRow) = Application.Sum(Range("A" & TopRow, "A" & CounterRow - 1))
                Range("A" & CounterRow).Font.Bold = True
                Range("A" & CounterRow).Interior.Color = vbYellow
                TopRow = CounterRow + 1
            End If
        Next CounterRow
        
    End Sub
    PLS DO NOT PM; OPEN A THREAD INSTEAD!!!

    1) Posting Code
    [CODE]PasteYourCodeHere[/CODE]
    (or paste your code, select it, click # button)

    2) Uploading File(s)
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) (multiple files can be selected while holding Ctrl key) / Upload Files / Done
    Replace company specific / sensitive / confidential data. Include so many rows and sheets etc in the uploaded workbook to enable the helpers visualize the data and table structure. Helpers do not need the entire workbook.

    3) Testing the Codes
    always back up your files before testing the codes.

    4) Marking the Thread as Solved
    from Thread Tools (on the top right corner, above the first message)

  5. #5
    Quote Originally Posted by mancubus View Post
    welcome to the forum

    try this:

    Sub vbax_69213_Sum_Cell_Values_Until_Blank_Cells()
    
        Dim LastRow As Long, CounterRow As Long, BottomRow As Long
        
        LastRow = Range("A" & Rows.Count).End(xlUp).Row 'assuming numbers to sum and blank sum cells are in col A
        BottomRow = LastRow
        
        For CounterRow = LastRow To 3 Step -1 'assuming first blank sum cell is A3
            If Range("A" & CounterRow) = "" Then
                Range("A" & CounterRow) = Application.Sum(Range("A" & CounterRow + 1, "A" & BottomRow))
                BottomRow = CounterRow - 1
            End If
        Next CounterRow
        
    End Sub
    Great, it works great. But at cell blank there is no sum function. I can't distinguish the original value. You are No.1

  6. #6
    VBAX Guru mancubus's Avatar
    Joined
    Dec 2010
    Location
    "Where I lay my head is home" :D
    Posts
    2,644
    see post #3
    PLS DO NOT PM; OPEN A THREAD INSTEAD!!!

    1) Posting Code
    [CODE]PasteYourCodeHere[/CODE]
    (or paste your code, select it, click # button)

    2) Uploading File(s)
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) (multiple files can be selected while holding Ctrl key) / Upload Files / Done
    Replace company specific / sensitive / confidential data. Include so many rows and sheets etc in the uploaded workbook to enable the helpers visualize the data and table structure. Helpers do not need the entire workbook.

    3) Testing the Codes
    always back up your files before testing the codes.

    4) Marking the Thread as Solved
    from Thread Tools (on the top right corner, above the first message)

Posting Permissions

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