I have an xls sheet with 500 rows and 4 columns. In column B is the date for
that particular record.
Is there a way that I can have xls insert a blank row everytime it
encounters a change in date in colum B. For example I may have 10 records
with the date 01-Sep-06, then after those 10 records that date wil be
02-Sep-06 and that may be 5 rows.
I want a blank row automatically inserted after each change in date.

Maybe this:

With your dates in Col_B, with B1 as the column heading

Then
In the cell to the right of your last column heading enter: DateCopy
(I'll assume D1)

D2: =B2
copy that formula down as far as you need

In cell E1 enter this: DeleteMe

Select the data range, including the 2 new columns
From the Excel main menu:
<data<subtotals
At each change in: DateCopy
Use Function: Sum
Add subtotalto : DeleteMe
Click the [OK] button
(That will create the subtotals...inserting a blank row whenever the data
changes)

Now...just delete the DateCopy and DeleteMe columns

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP


"Dtown Dawg" wrote:

I have an xls sheet with 500 rows and 4 columns. In column B is the date for
that particular record.
Is there a way that I can have xls insert a blank row everytime it
encounters a change in date in colum B. For example I may have 10 records
with the date 01-Sep-06, then after those 10 records that date wil be
02-Sep-06 and that may be 5 rows.
I want a blank row automatically inserted after each change in date.

try this
Sub insertrowatchange()
For i = Cells(Rows.Count, "b").End(xlUp).Row To 2 Step -1
If Cells(i, "b") < Cells(i - 1, "b") Then Rows(i).Insert
Next i
End Sub

--
Don Guillett
SalesAid Software

"Dtown Dawg" wrote in message
...
I have an xls sheet with 500 rows and 4 columns. In column B is the date
for
that particular record.
Is there a way that I can have xls insert a blank row everytime it
encounters a change in date in colum B. For example I may have 10 records
with the date 01-Sep-06, then after those 10 records that date wil be
02-Sep-06 and that may be 5 rows.
I want a blank row automatically inserted after each change in date.

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

If not familiar with VBA and macros, see David McRitchie's site for more on
"getting started".

http://www.mvps.org/dmcritchie/excel/getstarted.htm

In the meantime..........

First...create a backup copy of your original workbook.

To create a General Module, hit ALT + F11 to open the Visual Basic Editor.

Hit CRTL + r to open Project Explorer.

Find your workbook/project and select it.

Right-click and InsertModule. Paste the code in there. Save the
workbook and hit ALT + Q to return to your workbook.

Run the macro by going to ToolMacroMacros.

You can also assign this macro to a button or a shortcut key combo.


Gord Dibben MS Excel MVP


On Tue, 14 Nov 2006 12:35:01 -0800, Dtown Dawg
wrote:

I have an xls sheet with 500 rows and 4 columns. In column B is the date for
that particular record.
Is there a way that I can have xls insert a blank row everytime it
encounters a change in date in colum B. For example I may have 10 records
with the date 01-Sep-06, then after those 10 records that date wil be
02-Sep-06 and that may be 5 rows.
I want a blank row automatically inserted after each change in date.

Gord Dibben MS Excel MVP