I have a list of contacts thousands of rows long. I have sorted the list by
contact and each contact name can make up more than two rows and up to ten.

I need to add a blank line for every change in contact name in the column to
make the worksheet easier to read. The macros I tried to develop only has a
set place to add a row but I would like it to work similar to a subtotal
statement. In a subtotal statement, you can ask for a total at every change
in a certain column. For every change in the column I would like to add a
blank row. Is this possible?

I appreciate any assistance with this.

Hi RS. You can adapt the code at Ron DeBruin's site to insert rows rather
than delete. Use a helper column, assuming your contacts start in A2, in B2
type: =if(A2=A2+1,"","N") and copy down. Use column B in the code at:
http://www.rondebruin.nl/delete.htm
Sincerely, Michael Colvin


"RS" wrote:

I have a list of contacts thousands of rows long. I have sorted the list by
contact and each contact name can make up more than two rows and up to ten.

I need to add a blank line for every change in contact name in the column to
make the worksheet easier to read. The macros I tried to develop only has a
set place to add a row but I would like it to work similar to a subtotal
statement. In a subtotal statement, you can ask for a total at every change
in a certain column. For every change in the column I would like to add a
blank row. Is this possible?

I appreciate any assistance with this.

Thank you, Michael. I looked at the information and it would work if I had
cells that did not change value to look up.

Could I get the statement for the "subtotal" function in VBA and modify it?
Where would I get the subtotal code?

RS

"Michael" wrote:

Hi RS. You can adapt the code at Ron DeBruin's site to insert rows rather
than delete. Use a helper column, assuming your contacts start in A2, in B2
type: =if(A2=A2+1,"","N") and copy down. Use column B in the code at:
http://www.rondebruin.nl/delete.htm
Sincerely, Michael Colvin


"RS" wrote:

I have a list of contacts thousands of rows long. I have sorted the list by
contact and each contact name can make up more than two rows and up to ten.

I need to add a blank line for every change in contact name in the column to
make the worksheet easier to read. The macros I tried to develop only has a
set place to add a row but I would like it to work similar to a subtotal
statement. In a subtotal statement, you can ask for a total at every change
in a certain column. For every change in the column I would like to add a
blank row. Is this possible?

I appreciate any assistance with this.

RS

Sub InsertRow_At_Change()
Dim i As Long
With Application
.Calculation = xlManual
.ScreenUpdating = False
End With
For i = Cells(Rows.Count, 1).End(xlUp).Row To 2 Step -1
If Cells(i - 1, 1) < Cells(i, 1) Then _
Cells(i, 1).Resize(2, 1).EntireRow.Insert
Next i
With Application
.Calculation = xlAutomatic
.ScreenUpdating = True
End With
End Sub


Gord Dibben MS Excel MVP

On Wed, 18 Jan 2006 08:50:07 -0800, "RS" wrote:

I have a list of contacts thousands of rows long. I have sorted the list by
contact and each contact name can make up more than two rows and up to ten.

I need to add a blank line for every change in contact name in the column to
make the worksheet easier to read. The macros I tried to develop only has a
set place to add a row but I would like it to work similar to a subtotal
statement. In a subtotal statement, you can ask for a total at every change
in a certain column. For every change in the column I would like to add a
blank row. Is this possible?

I appreciate any assistance with this.