Alter this code to insert 3 blank rows?

[Coyotex3]

Good afternoon, stumped on this one. Here is the code that I have so far, but have not been able to add the missing piece.

Sub InsertBlank()
Dim i As Long
Dim LR As Long

LR = Range("A" & Rows.Count).End(xlUp).Row

    For i = 2 To LR
        If Range("A" & i).Value = "NYC" Then Range("A" & i).EntireRow.Insert

    Next
End Sub

Unsure of what I need to change here. I'm trying to insert 3 rows when Range("A" & i).value ="NYC"

Any ideas?

 

[Micron]

Not sure starting a new thread for the same thing will be appreciated by moderators, but I'm not one so I don't know. Anyway, what I last suggested won't work because once a For Next loop has begun, the start, end, step parameters cannot be changed (by a global variable) SO the solution is to use a Do While loop, as in

Sub InsertBlank()
Dim i As Long, n As Long, LR As Long

'NoEvents = True
LR = Range("A" & Rows.count).End(xlUp).Row

i = 2
Do While i < LR + 1
  Debug.Print Range("A" & i)
  If Range("A" & i) = "NYC" Then
    For n = 1 To 3
       Range("A" & i).EntireRow.Insert
       LR = LR + 1
       i = i + 1
    Next
  End If
  i = i + 1
Loop
'NoEvents = False

End Sub

I have other module code that that code will cause to run, which I don't want. So in a standard module (a code module that does not belong to a sheet) where I have
Option Explicit
I added
Public noevents As Boolean
You could try the above code with noevents commented out, put the cursor in the code somewhere and press F8 to initiate, and repeatedly press again to watch the flow. If you find that when inserting a row that this code branches out to some other procedure, you should note the names of any of those procedures as well as determine what the implications are if you allow that procedure to continue. If that happens and you want to stop it, insert a line at the beginning like
If noevents Then Exit Sub (or Exit Function).

You should comment out the debug.print line when you are finished testing.

 

[Coyotex3]

Not sure starting a new thread for the same thing will be appreciated by moderators, but I'm not one so I don't know. Anyway, what I last suggested won't work because once a For Next loop has begun, the start, end, step parameters cannot be changed (by a global variable) SO the solution is to use a Do While loop, as in

Sub InsertBlank()
Dim i As Long, n As Long, LR As Long

'NoEvents = True
LR = Range("A" & Rows.count).End(xlUp).Row

i = 2
Do While i < LR + 1
  Debug.Print Range("A" & i)
  If Range("A" & i) = "NYC" Then
    For n = 1 To 3
       Range("A" & i).EntireRow.Insert
       LR = LR + 1
       i = i + 1
    Next
  End If
  i = i + 1
Loop
'NoEvents = False

End Sub

I have other module code that that code will cause to run, which I don't want. So in a standard module (a code module that does not belong to a sheet) where I have
Option Explicit
I added
Public noevents As Boolean
You could try the above code with noevents commented out, put the cursor in the code somewhere and press F8 to initiate, and repeatedly press again to watch the flow. If you find that when inserting a row that this code branches out to some other procedure, you should note the names of any of those procedures as well as determine what the implications are if you allow that procedure to continue. If that happens and you want to stop it, insert a line at the beginning like
If noevents Then Exit Sub (or Exit Function).

You should comment out the debug.print line when you are finished testing.

Thanks Micron. Sorry, I meant to say that I would start a new thread in which I’ll try to split the invoices without the need of inserting blank rows. Did not mean to say that I would be reposting this one.

I’ll give this code a try shortly and report back.

Many thanks!

 

[Coyotex3]

Not sure starting a new thread for the same thing will be appreciated by moderators, but I'm not one so I don't know. Anyway, what I last suggested won't work because once a For Next loop has begun, the start, end, step parameters cannot be changed (by a global variable) SO the solution is to use a Do While loop, as in

Sub InsertBlank()
Dim i As Long, n As Long, LR As Long

'NoEvents = True
LR = Range("A" & Rows.count).End(xlUp).Row

i = 2
Do While i < LR + 1
  Debug.Print Range("A" & i)
  If Range("A" & i) = "NYC" Then
    For n = 1 To 3
       Range("A" & i).EntireRow.Insert
       LR = LR + 1
       i = i + 1
    Next
  End If
  i = i + 1
Loop
'NoEvents = False

End Sub

I have other module code that that code will cause to run, which I don't want. So in a standard module (a code module that does not belong to a sheet) where I have
Option Explicit
I added
Public noevents As Boolean
You could try the above code with noevents commented out, put the cursor in the code somewhere and press F8 to initiate, and repeatedly press again to watch the flow. If you find that when inserting a row that this code branches out to some other procedure, you should note the names of any of those procedures as well as determine what the implications are if you allow that procedure to continue. If that happens and you want to stop it, insert a line at the beginning like
If noevents Then Exit Sub (or Exit Function).

You should comment out the debug.print line when you are finished testing.

This one works!!!!

Thank you so much

 

[Micron]

You're welcome. Glad I could help.