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

D

Dr. Picou

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?
 
D

Dave Peterson

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
 
M

Martin P

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