[Solved] How to Sum dynamic multiple range

[bigme]

hello,
kindly help me please, how to sum data in dynamic multiple range? thank you.

 

[Lutfor Rahman Shimanto]

hello,
kindly help me please, how to sum data in dynamic multiple range? thank you.

View attachment 1085

Hello BigMe

Thanks for sharing your problem. I want to sum up multiple dynamic ranges. Assuming the row of each range may vary from time to time. I am presenting an Excel VBA Event Procedure that will trigger when any values change with column B.

Follow these steps:

Step 1: Right-click on the sheet name tab => Click on View Code => Paste the following code in the sheet module => Save.

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim totalCell As Range
    Dim totalRange As Range
    Dim cell As Range
    Dim sumTotal As Double
    Dim lastRow, startRowTemp As Long
   
    Set totalRange = Range("B2:B" & Cells(Rows.Count, 2).End(xlUp).Row)
   
    startRowTemp = 2
   
    If Not Intersect(Target, totalRange) Is Nothing Then

        For Each cell In totalRange

            If InStr(1, cell.Value, "Total") > 0 Then

                lastRow = cell.Row
               
                sumTotal = Application.WorksheetFunction.Sum(Range("C" & startRowTemp & ":C" & lastRow - 1))
               
                Set totalCell = Cells(lastRow, 3)
                totalCell.Value = sumTotal
                startRowTemp = lastRow + 1
               
            End If
           
        Next cell
    End If

End Sub

​

Step 2: Return to the Sheet => Make changes to see the desired output like the following GIF.

​

Hopefully, the shared code will fulfil your requirements. Good luck!

Regards
Lutfor Rahman Shimanto
ExcelDemy

 

[bigme]

Dear Lutfor,
thank you for your response, is this code can run if i copied to a module? because i want to merge & modify with other macros, thank you

regards,
bigMe

 

[Lutfor Rahman Shimanto]

Dear Lutfor,
thank you for your response, is this code can run if i copied to a module? because i want to merge & modify with other macros, thank you

regards,
bigMe

Dear BigMe

Thanks for your queries. Unfortunately, you can not keep that code in a module. You must keep the code in the sheet module because the previous code is an Event Procedure.

However, we can modify the previous code and turn that into a Sub-procedure.

Excel VBA Sub-procedure:

Sub SumDynamicMultipleRange()

    Dim totalCell As Range
    Dim totalRange As Range
    Dim cell As Range
    Dim sumTotal As Double
    Dim lastRow, startRowTemp As Long
   
    Set totalRange = Range("B2:B" & Cells(Rows.Count, 2).End(xlUp).Row)
   
    startRowTemp = 2

    For Each cell In totalRange

        If InStr(1, cell.Value, "Total") > 0 Then

            lastRow = cell.Row
           
            sumTotal = Application.WorksheetFunction.Sum(Range("C" & startRowTemp & ":C" & lastRow - 1))
           
            Set totalCell = Cells(lastRow, 3)
            totalCell.Value = sumTotal
            startRowTemp = lastRow + 1
           
        End If
       
    Next cell

End Sub

OUTPUT OVERVIEW:

​

I hope the Sub-procedure will fulfil your requirements. Good luck

Regards
Lutfor Rahman Shimanto