Excel 2002 : How to insert a bank row between different codes ?

[Guest]

Dear Sir,

May I know is there any simple formula or keyboard steps to insert an empty
row at all the columns of the worksheet as illustrated below :


Before Insert After Insert
A B A B
1 SNL224 xxx 1 SNL224 Xxx
2 SNL224 xxx 2 SNL224 Xxx
3 SNL224 xxx 3 SNL224 Xxx
4 TST144 xxx 4
5 TST144 xxx 5 TST144 Xxx
6 TST144 xxx 6 TST144 Xxx
7 UYT266 xxx 7 TST144 Xxx
8 UYT266 xxx 8
9 UYT266 xxx 9 UYT266 Xxx
10 KJH896 xxx 10 UYT266 Xxx
11 KJH896 xxx 11 UYT266 Xxx
12 KJH896 xxx 12
15 KJH896 Xxx
16 KJH896 Xxx
17 KJH896 Xxx


Thanks

Low

 

[Bernard Liengme]

Right click on the row header 4 (the 4 to the far left) and in the popup
menu use Insert
Does this answer the question?

 

[Guest]

Hello Bernard.

I could insert row this way, but I have to repeat the process many times as
the blocks for each code to be inserted are many.

Is there any formula ?

Thanks

Low

 

[Gord Dibben]

No simple formula or keyboard steps but a macro will work nicely.

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

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

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

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


Gord Dibben MS Excel MVP

Dear Sir,

May I know is there any simple formula or keyboard steps to insert an empty
row at all the columns of the worksheet as illustrated below :


Before Insert After Insert
A B A B
1 SNL224 xxx 1 SNL224 Xxx
2 SNL224 xxx 2 SNL224 Xxx
3 SNL224 xxx 3 SNL224 Xxx
4 TST144 xxx 4
5 TST144 xxx 5 TST144 Xxx
6 TST144 xxx 6 TST144 Xxx
7 UYT266 xxx 7 TST144 Xxx
8 UYT266 xxx 8
9 UYT266 xxx 9 UYT266 Xxx
10 KJH896 xxx 10 UYT266 Xxx
11 KJH896 xxx 11 UYT266 Xxx
12 KJH896 xxx 12
15 KJH896 Xxx
16 KJH896 Xxx
17 KJH896 Xxx


Thanks

Low

Gord Dibben MS Excel MVP

 

[Guest]

Hello Gord,

Thanks for your information.

Kind Regards

Low