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

Michael O'Flaherty
Michael O'Flaherty used Ask the Experts™
on
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
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
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

Michael O'FlahertySchool Business Manager

Author

Commented:
Excellent
Thanks Wayne

Michael
Rob HensonFinance Analyst

Commented:
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'FlahertySchool Business Manager

Author

Commented:
True Rob.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial