XL insert a blank row whenever a cell in column L changes

I have a 300 row sheet.
Whenever there is a change in the values in cells in Column L I want a blank row inserted before the change
Col-L-with-rows-inserted.xlsx
Michael O'FlahertyAsked:
Who is Participating?
 
Wayne Taylor (webtubbs)Commented:
Try this...

Sub InsertBlankRows()
    Dim rw As Long
    For rw = Cells(Rows.Count, "L").End(xlUp).Row To 2 Step -1
        If Cells(rw, "L").Value <> Cells(rw + 1, "L").Value Then
            Rows(rw + 1).Insert
        End If
    Next    
End Sub

Open in new window

0
 
Michael O'FlahertyAuthor Commented:
Excellent
Thanks Wayne

Michael
0
 
Rob HensonFinance AnalystCommented:
You could have also used the Subtotal wizard.

This inserts a row (and subtotal formula) after a change in a designated column. It also adds grouping icons to the left margin so that you can expand/collapse the groups.

If you don't need the subtotals, collapse to level 1 and delete them.
0
 
Michael O'FlahertyAuthor Commented:
True Rob.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.