Insert Blank Line
 

I have a column that has certain number information and I'm wanting to insert
a blank line each time the number changes.

Example:

004012
004012
004012
004013
004013
004014
004014

I want to put a blank line betwenn 004012 and 004013 and then again between
004013 and 004014.

Is there an easy way to do this? I have 700 records and the numbers are
different.

Thanks in advance for any help.

Sherry

Are you OK with a macro?

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(1, 1).EntireRow.Insert
Next i
With Application
.Calculation = xlAutomatic
.ScreenUpdating = True
End With
End Sub

Assumes column A is the one with the data.

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..........

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 above code in there. Save the
workbook and hit ALT + Q to return to your workbook.

Run the macro by going to ToolMacroMacros.


Gord Dibben Excel MVP

On Mon, 12 Sep 2005 13:24:01 -0700, Sherry
wrote:

I have a column that has certain number information and I'm wanting to insert
a blank line each time the number changes.

Example:

004012
004012
004012
004013
004013
004014
004014

I want to put a blank line betwenn 004012 and 004013 and then again between
004013 and 004014.

Is there an easy way to do this? I have 700 records and the numbers are
different.

Thanks in advance for any help.

Or if you are not OK with macros....
Arrange your data this way:

Help1 Help2 Data Criteria
1 0 4012 Test
2 0 4012 FALSE
3 1 4012
4 0 4013
5 1 4013
6 0 4014
7 4014

Tools Options General R1C1 Reference Style
Select the 8 row, 3 column array and do
Insert Name Create Top Row
Select the next 3 row vector and do
Insert Name Create Top Row
Fill Help2 with this formula:
=--NOT((Data R[1]=Data))
Fill the cell that says FALSE with =Help2=1
Data Filter Advanced Filter Copy To Another Selection
List Range Select the array as above
Criteria Range Type in Criteria
Copy To Select first empty cell below Help1 OK
Delete the Help2 column.
Format the appended data with a unique format, say red font.
Select all the data in the first 2 columns and sort by Help1.
Edit Replace Format red font Replace all

Hi sherry,

Pls try this in cell 'B2' insert function 'A2=A1', and drawdown the formula,
1. use filter in 'B' column and select 'false'
2. select 'B' column and press 'select visible cells' button
(for the commond button toolscutomizecommond tabeditselect visible cell.)
3. than insert row

"Sherry" wrote:

I have a column that has certain number information and I'm wanting to insert
a blank line each time the number changes.

Example:

004012
004012
004012
004013
004013
004014
004014

I want to put a blank line betwenn 004012 and 004013 and then again between
004013 and 004014.

Is there an easy way to do this? I have 700 records and the numbers are
different.

Thanks in advance for any help.

Gord,

Thank you for the marco. It worked like a charm.

Thanks again.

Sherry


"Gord Dibben" wrote:

Sherry

Are you OK with a macro?

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(1, 1).EntireRow.Insert
Next i
With Application
.Calculation = xlAutomatic
.ScreenUpdating = True
End With
End Sub

Assumes column A is the one with the data.

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..........

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 above code in there. Save the
workbook and hit ALT + Q to return to your workbook.

Run the macro by going to ToolMacroMacros.


Gord Dibben Excel MVP

On Mon, 12 Sep 2005 13:24:01 -0700, Sherry
wrote:

I have a column that has certain number information and I'm wanting to insert
a blank line each time the number changes.

Example:

004012
004012
004012
004013
004013
004014
004014

I want to put a blank line betwenn 004012 and 004013 and then again between
004013 and 004014.

Is there an easy way to do this? I have 700 records and the numbers are
different.

Thanks in advance for any help.

Thanks for the feedback.

Gord

On Tue, 13 Sep 2005 05:23:04 -0700, Sherry
wrote:

Gord,

Thank you for the marco. It worked like a charm.

Thanks again.

Sherry


"Gord Dibben" wrote:

Sherry

Are you OK with a macro?

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(1, 1).EntireRow.Insert
Next i
With Application
.Calculation = xlAutomatic
.ScreenUpdating = True
End With
End Sub

Assumes column A is the one with the data.

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..........

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 above code in there. Save the
workbook and hit ALT + Q to return to your workbook.

Run the macro by going to ToolMacroMacros.


Gord Dibben Excel MVP

On Mon, 12 Sep 2005 13:24:01 -0700, Sherry
wrote:

I have a column that has certain number information and I'm wanting to insert
a blank line each time the number changes.

Example:

004012
004012
004012
004013
004013
004014
004014

I want to put a blank line betwenn 004012 and 004013 and then again between
004013 and 004014.

Is there an easy way to do this? I have 700 records and the numbers are
different.

Thanks in advance for any help.

Anirudh:
A great improvement on my method.
Here is an alternate way without using Filter:
Select data in column B
Copy Paste Special Values OK Esc
Edit Go To Special Column differences
Insert Entire Row

Insert Blank Line
 

Gord - this works, but how can I change the code to only insert rows if
Column C does not equal the row above it?

thanks,
Aashish

"Gord Dibben" wrote:

Sherry

Are you OK with a macro?

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(1, 1).EntireRow.Insert
Next i
With Application
.Calculation = xlAutomatic
.ScreenUpdating = True
End With
End Sub

Assumes column A is the one with the data.

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..........

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 above code in there. Save the
workbook and hit ALT + Q to return to your workbook.

Run the macro by going to ToolMacroMacros.


Gord Dibben Excel MVP

On Mon, 12 Sep 2005 13:24:01 -0700, Sherry
wrote:

I have a column that has certain number information and I'm wanting to insert
a blank line each time the number changes.

Example:

004012
004012
004012
004013
004013
004014
004014

I want to put a blank line betwenn 004012 and 004013 and then again between
004013 and 004014.

Is there an easy way to do this? I have 700 records and the numbers are
different.

Thanks in advance for any help.

Insert Blank Line
 

Please stay in the SAME thread when replying.

--
Don Guillett
SalesAid Software

"aashish" wrote in message
...
Gord - this works, but how can I change the code to only insert rows if
Column C does not equal the row above it?

thanks,
Aashish

"Gord Dibben" wrote:

Sherry

Are you OK with a macro?

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(1, 1).EntireRow.Insert
Next i
With Application
.Calculation = xlAutomatic
.ScreenUpdating = True
End With
End Sub

Assumes column A is the one with the data.

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..........

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 above code in there. Save the
workbook and hit ALT + Q to return to your workbook.

Run the macro by going to ToolMacroMacros.


Gord Dibben Excel MVP

On Mon, 12 Sep 2005 13:24:01 -0700, Sherry

wrote:

I have a column that has certain number information and I'm wanting to
insert
a blank line each time the number changes.

Example:

004012
004012
004012
004013
004013
004014
004014

I want to put a blank line betwenn 004012 and 004013 and then again
between
004013 and 004014.

Is there an easy way to do this? I have 700 records and the numbers are
different.

Thanks in advance for any help.