Hi all,
I've been browsing these forums for a while -- it is such a wonderful resource. I was able to find some VBA codes that will allow me to add an extra row whenever the word "Transfer" appears and to sum the values below the blank row that was added. I need to adjust this range to include one extra cell after the blank row but can't seem to figure it out. Is it possible to look for the blank row + 1? Also, is it possible to combine this action into one macro, rather than having to run multiple macros to get the result I'm looking for? I've attached a copy of the test document I'm using.
Thanks! I appreciate your insight!
Code
Sub add_row() Dim x&
x = Cells(Rows.Count, "B").End(xlUp).Row
While x <> 0
If UCase(Cells(x, "B").Value) = UCase("transfer") Then
Rows(x).Insert
End If
x = x - 1
Wend
End Sub
Sub sum_cells()
Dim r As Range
For Each r In Columns("F").SpecialCells(2, 1).Areas
If r(0).Value = "" Then
With r(0).Resize(, 1)
.Formula = "=subtotal(9," & r.Address & ")"
.EntireRow.Range("a1:bg1").Interior.Color = RGB(255, 255, 153)
End With
End If
Next
End Sub
Display More