Search code examples
excelvbainsertrowexcel-365

Insert row before over a row containing a word vba excel


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


Solution

  • 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.