|
Excel 2002 : How to insert a bank row between different codes ?
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 -- A36B58K641 |
Excel 2002 : How to insert a bank row between different codes ?
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? -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "Mr. Low" wrote in message ... 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 -- A36B58K641 |
Excel 2002 : How to insert a bank row between different codes
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 -- A36B58K641 "Bernard Liengme" wrote: 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? -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "Mr. Low" wrote in message ... 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 -- A36B58K641 |
Excel 2002 : How to insert a bank row between different codes ?
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 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 05:51:02 -0800, Mr. Low wrote: 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 |
Excel 2002 : How to insert a bank row between different codes
Hello Gord,
Thanks for your information. Kind Regards Low -- A36B58K641 "Gord Dibben" wrote: 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 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 05:51:02 -0800, Mr. Low wrote: 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 |
| All times are GMT +1. The time now is 02:45 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com