Insert Blank Line

[Guest]

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 Dibben]

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

 

[Herbert Seidenberg]

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

 

[Guest]

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

 

[Guest]

Gord,

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

Thanks again.

Sherry

 

[Gord Dibben]

Thanks for the feedback.

Gord

Gord,

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

Thanks again.

Sherry

 

[Herbert Seidenberg]

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

 

[Guest]

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

 

[Don Guillett]

Please stay in the SAME thread when replying.