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

Run the macro by going to Tool>Macro>Macros.


Gord Dibben Excel MVP

On Mon, 12 Sep 2005 13:24:01 -0700, Sherry <[email protected]>
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 tools>cutomize>commond tab>edit>select 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 Insert>Module. Paste the above code in there. Save the
> workbook and hit ALT + Q to return to your workbook.
>
> Run the macro by going to Tool>Macro>Macros.
>
>
> Gord Dibben Excel MVP
>
> On Mon, 12 Sep 2005 13:24:01 -0700, Sherry <[email protected]>
> 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 <[email protected]>
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 Insert>Module. Paste the above code in there. Save the
>> workbook and hit ALT + Q to return to your workbook.
>>
>> Run the macro by going to Tool>Macro>Macros.
>>
>>
>> Gord Dibben Excel MVP
>>
>> On Mon, 12 Sep 2005 13:24:01 -0700, Sherry <[email protected]>
>> 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

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 Insert>Module. Paste the above code in there. Save the
> workbook and hit ALT + Q to return to your workbook.
>
> Run the macro by going to Tool>Macro>Macros.
>
>
> Gord Dibben Excel MVP
>
> On Mon, 12 Sep 2005 13:24:01 -0700, Sherry <[email protected]>
> 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.
>
>

Please stay in the SAME thread when replying.

--
Don Guillett
SalesAid Software
[email protected]
"aashish" <[email protected]> wrote in message
news:[email protected]...
> 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 Insert>Module. Paste the above code in there. Save the
>> workbook and hit ALT + Q to return to your workbook.
>>
>> Run the macro by going to Tool>Macro>Macros.
>>
>>
>> Gord Dibben Excel MVP
>>
>> On Mon, 12 Sep 2005 13:24:01 -0700, Sherry
>> <[email protected]>
>> 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.
>>
>>