Wednesday, August 31, 2011

Excel Macro: Insert a row above a cell that is equal to something


When this Macro is run, it inserts a new line above any cell that has value equal to "hello-there" in the first column

Option Explicit
Sub InsertRows()
    Dim i As Long
    i = 2
    'loop until a space is found in the first(1) column
    Do Until Trim(Cells(i, 1)) = ""
        'change the content to be matched
        If Cells(i, 1) = "hello-here" Then
            Cells(i, 1).EntireRow.Insert
            'row is inserted where match is found so
            'we need to jump one step down, otherwise we will keep looping..
            'we don't want to keep looping, right buddy?
            i = i + 2
        Else
            i = i + 1
        End If
    Loop
End Sub