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
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
End Sub

Open in new window

Michael O'FlahertyAuthor Commented:
Thanks Wayne

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.
Michael O'FlahertyAuthor Commented:
True Rob.
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.