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]

You could insert a For Next loop and repeat the insert 2 more times, or either of these vba methods, which might be more efficient

Insert multiple Rows using Excel and VBA | Exceldome

This tutorial explains and provides step by step instructions on how to insert multiple rows in a worksheet using Excel, VBA and Shortcut methods.

www.exceldome.com

 

[Micron]

Perhaps this?

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

LR = Range("A" & Rows.count).End(xlUp).Row
For i = 2 To LR
     If Range("A" & i).Value = "NYC" Then Range(i + 1 & ":" & i + 3).EntireRow.Insert
Next

End Sub

 

[Coyotex3]

Perhaps this?

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

LR = Range("A" & Rows.count).End(xlUp).Row
For i = 2 To LR
     If Range("A" & i).Value = "NYC" Then Range(i + 1 & ":" & i + 3).EntireRow.Insert
Next

End Sub

Micron, thank you. This is something along the lines of what I need. Is there a way of inserting the 3 blank rows above "NYC" as opposed to below it? Trying to use the offset property is not working for me.

 

[Micron]

I suppose so, but it just occurred to me that as soon as you insert rows, no matter where you insert, the value of the last row is no longer valid so the loop will terminate early?

 

[Coyotex3]

I suppose so, but it just occurred to me that as soon as you insert rows, no matter where you insert, the value of the last row is no longer valid so the loop will terminate early?

This one has me stumped honestly. I'll probably avoid inserting blank rows and look for another method of splitting the data.

Thank you so much for the help nonetheless!!

 

[Micron]

That issue is not hard to solve I think but if solved, guess what?
NYC moves down 3 rows and the value is found again, so 3 rows inserted, then guess what?
Very long execution of the code - until you run out of some 1+ million rows.
That won't happen if you insert above though.

EDIT - re your comment about splitting. Just looking to find/highlight values? Cannot make use of conditional formatting?

 

[Coyotex3]

That issue is not hard to solve I think but if solved, guess what?
NYC moves down 3 rows and the value is found again, so 3 rows inserted, then guess what?
Very long execution of the code - until you run out of some 1+ million rows.
That won't happen if you insert above though.

EDIT - re your comment about splitting. Just looking to find/highlight values? Cannot make use of conditional formatting?

Yes, when I was testing it earlier prior to posting the question I kept getting unwanted results where too many rows were being inserted like you said.

I don't think I can use conditional formatting. What I was trying to accomplish was:

Inserting 3 rows in order to be able to separate multiple invoices with the rows, so that I can Ctrl + Down and copy and paste each invoice into a new worksheet. Inserting 3 rows did not seem that daunting at first though lol.

 

[Micron]

Well, I guess I should have asked why in the beginning. Why not offset 4 (?) after the inserts? You would need a module level variable for the counter (LR) and add 3 to it each time you insert. I think that should work.

IF there are any module codes that affect the sheet it could cause unwanted results because Application.EnableEvents = False will not stop those from running. A global Boolean variable could be set to False and those procedures could look for it. If you know there are no such procedures then no worries - unless you forget down the road.

 

[Coyotex3]

Well, I guess I should have asked why in the beginning. Why not offset 4 (?) after the inserts? You would need a module level variable for the counter (LR) and add 3 to it each time you insert. I think that should work.

IF there are any module codes that affect the sheet it could cause unwanted results because Application.EnableEvents = False will not stop those from running. A global Boolean variable could be set to False and those procedures could look for it. If you know there are no such procedures then no worries - unless you forget down the road.

Thank you, Micron. My knowledge of VBA is fairly limited, so I do not know how to incorporate a module level variable.

I'm going to post a new thread here shortly to see if I can figure out how to get the final result I was trying to somewhat figure out..