Results 1 to 2 of 2
  1. #1
    Join Date
    Jan 2009
    Location
    Ohio, United States
    Posts
    167

    Unanswered: Insert row before row with condition

    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

  2. #2
    Join Date
    Jan 2002
    Location
    Bay Area
    Posts
    511
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •