VBA Subtotal(9,"range") based on another column value

Question

I would like to use some macro to populate cells with the formula subtotal(9,"range"), being the range dinamic, based on the lenght of the cell. sample

I need to populate in row "C" the formula subtotal(9;range) based on the values in column "B". It have to check the value on column B, and compare to the row below. If the value of the row below is equal or inferior, it must be "blank", if not, will create a formula =subtotal(9,range), the range will start the row below and go until find a lenght equal or inferior.

I do this manual, but sometimes i have more than 1000 rows. Many thanks.

Answer 1

In the question you mention to populate the row with the subtotal function only if current row is inferior or equal to next row, but in your sample you only populate when is inferior. So I coded it that way. It can be changed by replacing the > in If .Range("B" & next_row) > .Range("B" & r) Then to >=

Sub populate()

Dim r As Long, r2 As Long, last_row As Long
Dim next_row As Long, current_len As Long, test_len As Long
Dim rng As String

With ActiveSheet

last_row = .Cells(Rows.Count, 1).End(xlUp).Row

For r = 2 To last_row
    next_row = r + 1

    If .Range("B" & next_row) > .Range("B" & r) Then
       current_len = .Range("B" & r)
       
       'create range
       For r2 = r + 1 To last_row
            test_len = .Range("B" & r2)
            If current_len >= test_len Then
                rng = "C" & r + 1 & ":" & "C" & r2 - 1
                Exit For
            End If
        Next
    
        .Range("C" & r).Formula = "=SUBTOTAL(9," & rng & ")"
    End If
    
Next

End With
End Sub