insert line on first instance of of a value

Question

i am trying to insert a row on the first instance where the value in column y is above 60. Only one row.

i did a loop to insert multiple rows for anything above 60 but i dont need this.

having difficulty changing this

here's what i got

     Dim Col As Variant
    Dim BlankRows As Long
    Dim LastRow As Long
    Dim i As Long
    Dim StartRow As Long

    

        Col = "Y"
        StartRow = 1
        BlankRows = 1

            LastRow = Cells(Rows.Count, Col).End(xlUp).Row

            Application.ScreenUpdating = False

            With ActiveSheet
 For i = LastRow To StartRow + 1 Step -1
 If .Cells(i, Col) > 60 Then
 .Cells(i, Col).EntireRow.Insert shift:=xlDown

    
 End If
 Next i
 End With
 Application.ScreenUpdating = True
 End Sub

`first instance` - do you mean as you scan down the sheet or up ? Is the new row to be inserted below or above the row that is > 60. — CDP1802, May 26 '21 at 14:26
Reading code is a lot easier if you keep the indentation properly aligned (so that, for example, `For` and the corresponding `Next` have the same indent and the lines within the loop are further indented). — Joffan, May 26 '21 at 19:38

score 1 · Answer 1 · answered May 26 '21 at 14:20

1

You can use a GoTo to skip the following iterations once a true is done.

Dim Col As Variant
Dim BlankRows As Long
Dim LastRow As Long
Dim i As Long
Dim StartRow As Long

        Col = "Y"
        StartRow = 1
        BlankRows = 1

            LastRow = Cells(Rows.Count, Col).End(xlUp).Row

            Application.ScreenUpdating = False

With ActiveSheet
 For i = LastRow To StartRow + 1 Step -1
 If .Cells(i, Col) > 60 Then
 .Cells(i, Col).EntireRow.Insert shift:=xlDown
Goto: Close
 End If
 Next i
 End With

Close
 Application.ScreenUpdating = True

 End Sub

answered May 26 '21 at 14:20

Mark S.

1,474
1
6
20

or just an Exit For in your if clause will do the trick. – ceci May 26 '21 at 14:23
Hi @ceci I think the problem with using the `Exit` is it will close the subroutine before it gets a chance to turn screen updating back on, whereas the `GoTo:` allows the OP to still execute the part that does this. Otherwise this would be a good alternative and much simpler to include. – Mark S. May 26 '21 at 14:27
nope the "exit for" will just exit the for loop and then continue with the rest of the code. – ceci May 26 '21 at 14:41
pardon me, @ceci I missed the `For` part of the `Exit For`. This would also work, thanks for alternative, apologies for the oversight on my part. – Mark S. May 26 '21 at 17:48

Алексей Р · Accepted Answer · 2021-05-26T19:38:31.723

You can use Do loop for this (2 search direction variants):

Option Explicit

Sub InsertRowSearchDown()
    Dim out As Boolean, cl As Range
    
    Set cl = ActiveSheet.Range("Y1")    'start cell
    Do Until out                        'initially out=False
        If cl > 60 Then
            cl.EntireRow.Insert
            out = True
        Else
            Set cl = cl.Offset(1)       'move DOWN to the next row
            out = IsEmpty(cl)
        End If
    Loop
End Sub

Sub InsertRowSearchUp()
    Dim out As Boolean, cl As Range
    
    Set cl = ActiveSheet.Range("Y" & Rows.Count).End(xlUp)        'start cell
    Do Until out                            'initially out=False
        If cl > 60 Then
            cl.EntireRow.Insert
            out = True
        Else
            If cl.Row = 1 Then
                out = True
            Else
                Set cl = cl.Offset(-1)          'move UP to the previous row
            End If
        End If
    Loop
End Sub

insert line on first instance of of a value

2 Answers2