inserting rows - above or below current lines

[Todd77]

Hi all,

I'm a very new VBA user, so this question might seem a little simple and obvious.

When recently looking for code to create blank rows based on cell values, I noticed that all codes read the rows of data from bottom to top, inserting blank rows above the rows already there instead of inserting them below. Why is this the case? Is that a programming standard? Does it make a difference how the code reads the data and where the blank rows are inserted relative to the cells containing the values on which the code is acting?

Todd77

 

[Fluff]

To insert a blank row below a certain value you can use something like

Sub Todd77()
   Dim i As Long
   
   For i = Range("A" & Rows.Count).End(xlUp).Row To 2 Step -1
      If Cells(i, 1).Value = "abc" Then Rows(i + 1).Insert
   Next i
End Sub

When inserting/deleting rows it's generally better to work from bottom to top.

 

[Todd77]

But why? Does the process crash if it were to read the code from top to bottom and insert rows below instead of above?

 

[jasonb75]

It wouldn't crash but it would only work correctly for the first line, unless it is written very carefully (and inefficiently).

When you loop through the rows from top to bottom, the inserted rows become part of the loop. Working with just rows 2 and 3.

Select row 2 and insert a new row above, the original row 2 moves down to row 3 and row 3 moves down to row 4.
For the next pass of the loop the code will be working with row 3, which is now the row that was in row 2, meaning that the row inserted above what is now row 3 is actually inserted above the original row 2 (again) instead of above row 4 where it should be.
Nothing is inserted above the original row 3 because once it moved down to row 4, it was outside of the loop.

By working from bottom to top, the rows that have been changed are below the one to do next, so the loop is not affected by the previous pass.

 

[Fluff]

Adding to what Jason has said, if you were deleting rows & went top down, there are situations where you could end up in a permanent loop, ie one that never ever ends until Xl has had enough & crashes.

 

[jasonb75]

Thanks for adding that, Fluff. That was the bit that I hadn't thought of.

 

[Todd77]

That all makes sense. Thanks a lot for the explanations, Jasonb75 and Fluff.

 

[Fluff]

You're welcome & thanks for the feedback