I have a spreadsheet with a unique identifier for each
customer, Customer Information File (CIF). Whenever this
number changes, I want to automatically insert a blank
row. This will merely making the the spreadsheet more
pleasing to the eye to read. Keep in mind, there may not
be a break in CIF for 1 to 15 lines, but whenever this
CIF number changes, I want a new blank row.

Any help would be greatly appreciated!!

David

David

Macro solution OK?

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

Assumes the ID numbers are in column C. If in another column, change the 3's
in the code to whatever column number you need.


Gord Dibben Excel MVP

On Wed, 4 May 2005 06:55:34 -0700, "David Noel" wrote:

I have a spreadsheet with a unique identifier for each
customer, Customer Information File (CIF). Whenever this
number changes, I want to automatically insert a blank
row. This will merely making the the spreadsheet more
pleasing to the eye to read. Keep in mind, there may not
be a break in CIF for 1 to 15 lines, but whenever this
CIF number changes, I want a new blank row.

Any help would be greatly appreciated!!

David

Is this an existing file with no breaks you want to change so that there are
breaks or is this a file you are generating and when somone is entering the
CIF it will automatically go down an extra line when a new one is added?

If it is an existing file one way to do it would be to temporarily add a
column next to the CIF column(for demonstation assume columns a and b
starting in A2
put 1 in the B2
below this enter
=if(A3=A2,B2,B2+1)
copy this cell down to end of the data
copy column b and paste special values on on itself
below the lowest spot with data in column B enter a series starting 1.5 and
ending your largest number in B +.5 in increments of 1
now sort on column B and you will have the lines between the CIFs.

(it is not nearly as complicated as it sounds)

You can also do it with a macro



"David Noel" wrote:

I have a spreadsheet with a unique identifier for each
customer, Customer Information File (CIF). Whenever this
number changes, I want to automatically insert a blank
row. This will merely making the the spreadsheet more
pleasing to the eye to read. Keep in mind, there may not
be a break in CIF for 1 to 15 lines, but whenever this
CIF number changes, I want a new blank row.

Any help would be greatly appreciated!!

David

Instead of adding blank rows, you could use conditional formatting to
shade the rows where the CIF changes. For example:

Select your data, starting in row 2 (cells A2:G1000 in this example)
Choose FormatConditional Formatting
From the first dropdown, choose Formula Is
In the formula box, enter a formula that refers to the cell that
contains the CIF (C2):
=$C2<$C1
Click the Format button, and on the Patterns tab, select a colour
Click OK, click OK

This will make it easier to spot the customers in the list, and won't
interfere with other features, such as filtering, sorting and creating
pivot tables.


David Noel wrote:
I have a spreadsheet with a unique identifier for each
customer, Customer Information File (CIF). Whenever this
number changes, I want to automatically insert a blank
row. This will merely making the the spreadsheet more
pleasing to the eye to read. Keep in mind, there may not
be a break in CIF for 1 to 15 lines, but whenever this
CIF number changes, I want a new blank row.

Any help would be greatly appreciated!!

David


--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

I tried the Conditional Formatting, but it didn't seem to work for me. The
Macro did work.