easy macro to insert rows?

[neov]

My rust is showing....

I have a spreadsheet that has multiple columns...column B has either a 1, a 2, or an "#N/A".

I need to insert a row under any row that has a "2" in Column B.

I need to do this on every worksheet in the spreadsheet.

Any way to do this? Thanks in advance.

 

[Michael M]

try

Sub addrow()
Dim lr As Long, r As Long, ws As Worksheet
    For Each ws In Worksheets
        ws.Activate
        lr = Cells(Rows.Count, "B").End(xlUp).Row
            For r = lr To 1 Step -1
                If Range("B" & r).Value = 2 Then
                    Rows(r + 1).EntireRow.Insert
                End If
            Next r
     Next ws
End Sub

 

[neov]

getting a 'type mismatch' error message on this line:

If Range("I" & r).Value = 2 Then


I was wrong about the column - Column I is the column in question, not column B, so I changed the B to an I on both lines

 

[Rick Rothstein]

Give this macro a try (it should execute quite quickly)...

Sub InsertRowUnder2s()
  Application.ScreenUpdating = False
  With Columns("I")
    .Replace 2, "TRUE", xlWhole
    With .SpecialCells(xlCellTypeConstants, xlLogical)
      .Offset(1).EntireRow.Insert
      .Value = 2
    End With
  End With
  Application.ScreenUpdating = True
End Sub

EDIT NOTE: I updated the above code to reflect the column change you mentioned in one of your responses.

 

[Michael M]

Changing the "B" to an "I" should make no difference, as long as you did use an "I" and not a 1....and make sure that the 2 is a number and not a text "2"