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


On Wed, 23 Jan 2008 11:14:00 -0800, Leoc wrote:

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.

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.
--
Cheers,
Shane Devenshire


"Leoc" wrote:

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.