I am trying to insert a blank row above a row that contains a specific word. But so far I can only insert it below this row.
Sub INSERTROW()
Dim c As Range
Dim lRow As Long
lRow = 1
Dim lRowLast As Long
Dim bFound As Boolean
With ActiveSheet
lRowLast = .Cells(.Rows.Count, 1).End(xlUp).Row
Do
Set c = .Range("A" & lRow)
If c.Value Like "*SEARCHED VALUE*" Then
bFound = True
ElseIf bFound Then
bFound = False
If c.Value <> "BLANKROW" Then
c.EntireRow.Insert
lRowLast = lRowLast + 1
c.Offset(-1, 0).Value = "BLANKROW"
c.Offset(-1, 0).Font.Color = RGB(0, 0, 0)
End If
End If
lRow = lRow + 1
Loop While lRow <= lRowLast + 1
End With
End Sub
It's easier and faster (AFAIK) if you use the find method.
See that I insert the row where the value is found and then I refer to the previous row with the offset function.
Finally, as a good practice, try to name your procedures and variables to something meaningful and indent your code (you may use www.rubberduckvba.com)
Public Sub InsertRowBeforeWord()
Dim findString As String
findString = "*SEARCHED VALUE*"
Dim lastRow As Long
lastRow = ActiveSheet.Cells(ActiveSheet.Rows.Count, 1).End(xlUp).Row
Dim searchRange As Range
Set searchRange = ActiveSheet.Range("A1:A" & lastRow)
Dim returnRange As Range
Set returnRange = searchRange.Find(What:=findString, _
After:=searchRange.Cells(searchRange.Cells.Count), _
LookIn:=xlValues, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)
If Not returnRange Is Nothing Then
returnRange.Offset(0, 0).EntireRow.Insert
returnRange.Offset(-1, 0).Value = "BLANKROW"
returnRange.Offset(-1, 0).Font.Color = RGB(0, 0, 0)
End If
End Sub
Let me know if it works.