How to insert rows based on change in data

  • Thread starter Thread starter Leoc
  • Start date Start date
L

Leoc

I have a coulumn of data that with several rows of the same data and then the
data changes for several other rows and so on down the sheet. I need to be
able to selcet the column and insert a row between where the data changes.
Almost like doing a subtotal but just inserting a blank row to break up the
data.
 
Assumes the column is A..........(rows.count, 1) change the 1 to suit.

Sub InsertRow_At_Change()
'Sandy Mann July 1st, 2007
Dim LastRow As Long
Dim X As Long
LastRow = Cells(Rows.Count, 1).End(xlUp).Row
Application.ScreenUpdating = False

For X = LastRow To 3 Step -1
If Cells(X, 1).Value <> Cells(X - 1, 1).Value Then
If Cells(X, 1).Value <> "" Then
If Cells(X - 1, 1).Value <> "" Then
Cells(X, 1).EntireRow.Insert Shift:=xlDown
End If
End If
End If
Next X
Application.ScreenUpdating = True
End Sub


Gord Dibben MS Excel MVP
 
Hi,

Here is a non-VBA approach:
I am assuming that the items which repeat are in column A.


1. Add a temporary column and enter a formula like the following in C2 the
temporary column:
=IF(A2=A1,0,"T")
2. Copy the formula down. This should put a T on each row where you want to
insert a row.
3. Select from cell C3 down to the bottom and press F5, Special, Formulas,
and uncheck all but Text. Click OK.
4. Press Ctr+ (Ctrl and the Plus key on the number keypad) or Ctrl Shift+
if using the tip row of numbers. Choose Entire Row.
5. Remove the dummy column.

If you code this you will find it is faster than the For loop approach.
 
Back
Top