Can I use "find / replace" to automatically "Insert Rows" in a do.

Have a long column of numbers
Every time I see the number 5, I need to insert two rows. Doing this by
hand is tedious. Can you help me find a shortcut?
The IF function does not do this, right?

Not 25, 35, 5351, just plain old 5?

Option Explicit
Sub testme01()

Dim FirstRow As Long
Dim LastRow As Long
Dim iRow As Long

With Worksheets("sheet1")
FirstRow = 1
LastRow = .Cells(.Rows.Count, "C").End(xlUp).Row

For iRow = LastRow To FirstRow Step -1
If .Cells(iRow, "C").Value = 5 Then
.Rows(iRow + 1).Resize(2).Insert
End If
Next iRow
End With

End Sub

I used column C. Change that to what you need in two spots!

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Dr. Picou wrote:
>
> Have a long column of numbers
> Every time I see the number 5, I need to insert two rows. Doing this by
> hand is tedious. Can you help me find a shortcut?
> The IF function does not do this, right?

--

Dave Peterson

I believe this worksheet method will also take much less time than doing the
insertion by hand. The rows are inserted before the 5. If you need them after
the 5 a slight modification should not be too difficult.
I have assumed your values are in column B.
Cell A1: =ROW($B1)+D1
Cell C1: =IF(B1=5,2,0)
Cell D1: =SUM($C$1:C1)
Cell F1: =ROW(B1)-ROW($B$1)+1
Cell G1: =VLOOKUP(F1,$A$1:$B$35,2,FALSE)
Copy C1 to G1 as far down as necessary and also copy A1 down as far as
necessary.
Copy what you have in column G and use Edit, Paste Special to paste values
to a different worksheet. Use Edit, Replace to replace #N/A with nothing.
"Dr. Picou" wrote:

> Have a long column of numbers
> Every time I see the number 5, I need to insert two rows. Doing this by
> hand is tedious. Can you help me find a shortcut?
> The IF function does not do this, right?