Insert two lines each time a value in Column L changes

[ryguy7272]

I am trying to get Excel to insert two lines each time a value in Column L
changes. My code is below:

Dim rngB As Range
Set rngB = Range("L2")
While rngB.Value <> ""
If rngB.Value <> rngB.Offset(1).Value Then
rngB.Offset(1).Resize(2).EntireRow.Insert
Set rngB = rngB.Offset(1)
End If
Set rngB = rngB.Offset(1)
Wend

It works the first time, but then fails afterwards. Is this one of those
instances where you have to start from the bottom of the list and work up? I
though working from the bottom up was only necessary for deleting rows based
on some criteria, such as deleting rows with blanks.

Any suggestions?

Thanks a lot!!
Ryan--

 

[Don Guillett]

Start from the bottom up such as

Sub insertrowsifvaluechanges()
For i = Cells(Rows.Count, "L").End(xlUp).Row To 2 Step -1
If Cells(i, "L") <> Cells(i - 1, "L") Then Rows(i).Resize(2).Insert
Next i
End Sub

 

[ryguy7272]

Awesome! That's why you're an MVP.
Ryan--

 

[Tom Ogilvy]

Don showed you how to do it by looping backwards, but you can do it going
forward just by adjusting your macro to properly positioning rngB:

Sub AAA()
Dim rngB As Range
Set rngB = Range("L2")
While rngB.Value <> ""
If rngB.Value <> rngB.Offset(1).Value Then
rngB.Offset(1).Resize(2).EntireRow.Insert
Set rngB = rngB.Offset(2)
End If
Set rngB = rngB.Offset(1)
Wend

End Sub

 

[Don Guillett]

Aw shucks, Glad to help.