Insert row before row with condition

scrtchmstj · #1 (**permalink**) 03-14-11, 13:06

I'm trying to create a code that inserts a row before any row where the value in column M = 1. The code I tried using is as follows:

Code:

    'THIS SHOULD INSERT A ROW BEFORE ALL ROWS
    'WITH A VALUE OF 1 IN COLUMN M
    Dim FoundCell As Range
    Set FoundCell = Range("M:M").Find(what:=1)
    Do Until FoundCell Is Nothing
        FoundCell.EntireRow.Insert
        Set FoundCell = Range("M:M").FindNext
    Loop

The problem with this code (I think) is that it is inserting a row and then proceeding to search the next row and finding the row that was just shifted down. This macro runs out of control until I Ctrl Break. Any ideas how I could get it progress 1 row before it searches for the next row where M=1?

Thanks,
Josh

JerryDal · #2 (**permalink**) 03-15-11, 18:33

It appears that your code is continually finding the first occurrence of the value 1. The code below should work for you if no one else can correct your code. It assumes your data starts in row #2 and you start out with no blanks in column M.

Jerry

Code:

Sub Macro1()
'insert a blank row above every row that has the value 1 in column M
Dim i As Integer
   
    i = 2
    While Cells(i, 13) <> ""
        If Cells(i, 13) = 1 Then
            Rows(i).EntireRow.Insert
            i = i + 1
        End If
        i = i + 1
   Wend

End Sub

Internet Services

Web Development

Digital Marketing

Consumer Tech