Insert 5 Row if a Certain Value in Column A Q
I want to insert 5 blank rows, above the first time 10000 appears in Column A
Then Subtotal Column E & F (from Row 9 down) on the 2nd Row of the inserted rows, to finish this line off add a Top border to both cells
Finally a simple E-F formula on this Row in Column H
I've found code below, but this inserts a row on each "10000" that appears in Column A (there are multiple rows showing 10000, I just want the first one)
Sub BlankLine()
Dim Col As Variant
Dim BlankRows As Long
Dim LastRow As Long
Dim R As Long
Dim StartRow As Long
Col = "A"
StartRow = 1
BlankRows = 1
LastRow = Cells(Rows.Count, Col).End(xlUp).Row
Application.ScreenUpdating = False
With ActiveSheet
For R = LastRow To StartRow + 1 Step -1
If .Cells(R, Col) = "10000" Then
..Cells(R, Col).EntireRow.Insert Shift:=xlDown
End If
Next R
End With
Application.ScreenUpdating = True
End Sub
|