Sum dynamic ranges based on numbers in a helper column
I have a sheet with a list of numbers I want to sum. There are varying ranges after I have imported the data and am struggling to find the ranges and insert the totals(sum) of each category. There is a Helper column in column "A" which has the number 4 where the category starts and the number 7 where it ends. I want to sum the numbers in column "I" for rows between 4 (where 4 appears in column "A") and 7 (where 7 appears in column "A") and place the total in column "I" in the row that 7 appears in column "A". Here is the code I have so far.
Sub Demo1() Dim Rf As Range, Rg As Range, A$ With ActiveSheet.UsedRange.Columns(1) Set Rf = .Find(4, , xlValues, xlWhole) If Not Rf Is Nothing Then A = Rf.Address Application.ScreenUpdating = False Do Set Rg = .Find(7, Rf): If Rg.Row < Rf.Row Then Exit Do If Rg.Row > Rf.Row + 1 Then Rg(1, 9).Value2 = Application.Sum(Range(Rf(2, 9), Rg(0, 9))) Set Rf = .Find(4, Rg) Loop Until Rf.Address = A Set Rf = Nothing: Set Rg = Nothing Application.ScreenUpdating = True End If End With End Sub
Do you like it ? So thanks to click on bottom left star icon « ★ Add Reputation » !
Last edited by Marc L; 11-13-2019 at 07:43 AM.
Reason: optimizing for values instead of formulas …
Bookmarks