Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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? |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
changing formulas to values so that they will be recognized by Find and Replace | Excel Discussion (Misc queries) | |||
Find and replace of word causes change of font formatting | New Users to Excel | |||
Can I find and replace "white space" in a cell in Excel? | Excel Worksheet Functions | |||
is it possible to Restrict Find & Replace to 1 column | New Users to Excel | |||
Can I use Find and Replace all with Hyperlinks? | Excel Discussion (Misc queries) |