MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Insert Blank Rows after change in text


Posted by john v. on January 07, 2002 9:01 AM

How can I automate inserting a blank row after a change in text. I have a list that has repetive group names and i want to insert a blank row after each change in the group name. It is very tedious to manually go down the list and use the insert row command.


Posted by Tom Dickinson on January 07, 2002 10:59 AM

John:
Highlight the first cell you want compared in the column where you have the text to compared. then run the following macro. This will put in 1 blank row (if there isn't one already), and continue down the column until it encounters 2 blank cells. At that point it will end.

Sub Macro1()
Do Until ActiveCell = Empty And ActiveCell.Offset(1) = Empty
If ActiveCell <> ActiveCell.Offset(1) And ActiveCell <> Empty And ActiveCell.Offset(1) <> Empty Then
ActiveCell.Offset(1).EntireRow.Insert
ActiveCell.Offset(2).Select
Else
ActiveCell.Offset(1).Select
End If
Loop
End Sub

Hope that helps.

Posted by johnv on January 08, 2002 7:33 AM

Tom,
Worked like a champ. Thanks so much!