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? |
All times are GMT +1. The time now is 06:04 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com