Dynamic first and last row of a range

0
votes

I am surprised there's no answer for this. I have read Setting Dynamic Ranges in VBA and Selecting Dynamic Range and Autofill Dynamic Range Last Row and Last Column and MSDN

I have multiple, distinct ranges on a sheet with varying sizes. I am trying to subtotal column L. I can do it using a hardcoded sum (via subtotal variable) but I want to insert a formula into the cell instead. This requires knowing the starting and end rows for each range. My code almost works. It fails when the range only consists of one row. Even so, I feel there's gotta be a smarter way to do this.

How does one determine the start and end row of a range on a sheet filled with multiple ranges?

For i = 2 To j

    If .Cells(i + 1, "L") = "" And .Cells(i + 2, "L") = "" Then
        b = .Cells(i - 1, "J").End(xlUp).Row
    End If

    subtotal = subtotal + .Cells(i, "L").Value2
    If .Cells(i, 1) = "" And .Cells(i - 1, "B") <> "" Then
        If .Cells(i - 1, "K") = 0 Then
            .Cells(i, "K").Value2 = "Check Payment"
            'Set sumRng = .Range(.Cells(b, "L"), .Cells(i - 1, "L"))
            .Cells(i, "L").Formula = "=sum(L" & b & ":L" & i - 1 & ")"
            .Cells(i - 1, "L").Borders(xlEdgeBottom).LineStyle = xlContinuous
            total = total + subtotal
            subtotal = 0
        ElseIf .Cells(i - 1, "K") = "Checking" Then
            .Cells(i, "K").Value2 = "EFT Payment"
            'Set sumRng = .Range(.Cells(b, "L"), .Cells(i - 1, "L"))
            .Cells(i, "L").Formula = "=sum(L" & b & ":L" & i - 1 & ")"
            .Cells(i - 1, "L").Borders(xlEdgeBottom).LineStyle = xlContinuous
            total = total + subtotal
            subtotal = 0
        End If
    End If
Next

enter image description here

2
excelvba

2 Answers

1
votes

You can loop through the column like this:

For i = 2 To mySheet.Range("B" & Rows.Count).End(xlUp).Row + 1
    If Range("B" & i).Value <> vbNullString Then
        If Range("B" & i - 1).Value = vbNullString Then
            j = i
        End If
    Else
        If Range("B" & i - 1).Value <> vbNullString And Range("B" & i - 1).Formula <> "=SUM(B" & j & ":B" & i - 2 & ")" Then
            Range("B" & i).Formula = "=SUM(B" & j & ":B" & i - 1 & ")"
        End If
    End If
Next i
1
votes

This uses Match to skip chunks and as such the number or loops are less

With ActiveSheet
    Dim b As Long
    b = 2

    Do Until b = .Rows.Count

        Dim x As Variant
        x = .Evaluate("Match(True, Index(" & .Range(.Cells(b, "l"), .Cells(.Rows.Count, "l")).Address & " <> """",),0)")
        If Not IsError(x) Then
            b = b + x - 1
        Else
            Exit Sub
        End If

        x = .Evaluate("Match(True, Index(" & .Range(.Cells(b, "l"), .Cells(.Rows.Count, "l")).Address & " = """",),0)")
        Dim i As Long
        i = b + x - 1

        .Cells(i, "l").Formula = "=sum(L" & b & ":L" & i - 1 & ")"

        b = i + 2
     Loop
End With