Inserting rows when cell is a certain value

[rossa]

How do I insert rows when certain values are in a cell?
My data range has a colum (let's say column M) that contains a formula, can I create a macro that will insert a new row when ever the cell in Column M is equal to 1?

 

[Luke M]

How's this?

Sub InsertRows()
Dim myValue As Long
Dim lastRow As Long
Dim i As Long
Dim searchCol As String

'Get input from user
myValue = InputBox("What is the macro looking for?", "Look for", 1)
searchCol = InputBox("What column to search in?", "Search Col", "M")

Application.ScreenUpdating = False
With ActiveSheet
    'Determine where the last row is
    lastRow = .Cells(.Rows.Count, searchCol).End(xlUp).Row
    'Loop through the rows, starting at bottom and going up
    'since we will be inserting new rows
    For i = lastRow To 1 Step -1
        'Check if we found a match
        If .Cells(i, searchCol).Value = myValue Then
            .Cells(i, 1).EntireRow.Insert
        End If
    Next i
End With
Application.ScreenUpdating = True
   
End Sub

 

[rossa]

Great, thanks Luke.