VBA SUM Variable Range
17,664
Sub Test()
Dim y As Variant
Dim firstRow As Variant
Dim lastRow As Variant
lastRow = Range("C" & Rows.Count).End(xlUp).Row
firstRow = Cells(lastRow, 3).End(xlUp).Row
If IsNumeric(Cells(lastRow + 1, 1)) And IsEmpty(Cells(lastRow + 1, 2)) Then
Cells(lastRow + 1, 3).Formula = "=SUM(C" & firstRow & ":C" & lastRow & ")"
End If
For y = firstRow To 3 Step -1
lastRow = Cells(y, 3).End(xlUp).Row
firstRow = Cells(lastRow, 3).End(xlUp).Row
If firstRow < 3 Then firstRow = 3
If IsNumeric(Cells(lastRow + 1, 1)) And IsEmpty(Cells(lastRow + 1, 2)) Then
Cells(lastRow + 1, 3).Formula = "=SUM(C" & firstRow & ":C" & lastRow & ")"
End If
y = firstRow
If firstRow = 3 Then Exit Sub
Next y
End Sub
Author by
Nicholas Kan
Updated on July 20, 2022Comments
-
Nicholas Kan almost 2 years
I want a code to sum the Variable rows up if certain condition is met. e.g. If A12 is numeric and B12 is empty then insert a fomula in cell C12 to sum C3:C11. Then perform the same action at C22 and C30. The problem I have is don't know how to define the starting row.
Sub Test() Dim y As Variant Dim r As Variant Dim StartRow As Variant LastRow = Range("C" & Rows.Count).End(xlUp).Row For y = 3 To 500 For r = 1 To LastRow If InStr(1, Cells(r, 1), "Amount") Then StartRow = r If IsNumeric(Cells(y, 1)) And IsEmpty(Cells(y, 2)) Then Cells(y, 3).Formula = "=SUM(C" & StartRow + 1 & ":C" & y - 1 & ")" End If End If Next r Next y End Sub