Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Insert a row after next 100 rows
d'all,
I hav a sheet of 5000 rows. I want to insert a blank row after next 100 rows. I don't want to insert row one by one bcz of time consuming. Plz give me any solution for that. |
#2
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Insert a row after next 100 rows
Hi,
If you just need to do this once you can do the steps manually, but if you might have to do it more than once, then the code below may be useful. The following code inserts a row at every 100th line. To use it for any other number of lines simply change the 100 in the first row of code to the number you want. To use it select a vacant range of cells in a single column corresponding to the height of the range you want to insert blank rows iinto and run the macro. Tools Macro Macros. Sub InsertRows() Selection = "=1/MOD(ROW(),100)" Selection = Selection.Value Selection.SpecialCells(xlCellTypeConstants, 16).EntireRow.Insert Selection.EntireColumn.Delete End Sub This code runs extremely fast. To add this code to a workbook: 1. Press Alt+F11 (opens the Visual Basic Editor) 2. Select your file in the Project - VBA Project window, top left (press Ctrl+R if you do not see it.) 3. Choose Insert Module 4. Copy the code above to the module on the right If this helps, please click the Yes button. The manual step a 1. Enter this formula on row 1 =1/MOD(ROW(),100) 2. Copy it down 5000 rows 3. Select all the formulas and copy them 4. With them selected choose Edit, Paste Special, Values 5. Press F5, Special, Constant, and uncheck all except Errors, click OK 6. Press Ctrl+- (control minus) and choose Entire row. 7. Clear the contents of the column where you put the formulas. Shane Devenshire "Sandeep Jangra" wrote: d'all, I hav a sheet of 5000 rows. I want to insert a blank row after next 100 rows. I don't want to insert row one by one bcz of time consuming. Plz give me any solution for that. |
#3
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Insert a row after next 100 rows
HI,
I used 2nd option just want to do once. I pasted that formula in cell A1. I have 1st row for heading. when I pressed ctrl++ and then select entire row. insert a blank row after 98 rows (not 100 rows) except heading row. And next blank rows are right. But i want to insert a blank after 100 data rows. Plz give me solution. "Shane Devenshire" wrote: Hi, If you just need to do this once you can do the steps manually, but if you might have to do it more than once, then the code below may be useful. The following code inserts a row at every 100th line. To use it for any other number of lines simply change the 100 in the first row of code to the number you want. To use it select a vacant range of cells in a single column corresponding to the height of the range you want to insert blank rows iinto and run the macro. Tools Macro Macros. Sub InsertRows() Selection = "=1/MOD(ROW(),100)" Selection = Selection.Value Selection.SpecialCells(xlCellTypeConstants, 16).EntireRow.Insert Selection.EntireColumn.Delete End Sub This code runs extremely fast. To add this code to a workbook: 1. Press Alt+F11 (opens the Visual Basic Editor) 2. Select your file in the Project - VBA Project window, top left (press Ctrl+R if you do not see it.) 3. Choose Insert Module 4. Copy the code above to the module on the right If this helps, please click the Yes button. The manual step a 1. Enter this formula on row 1 =1/MOD(ROW(),100) 2. Copy it down 5000 rows 3. Select all the formulas and copy them 4. With them selected choose Edit, Paste Special, Values 5. Press F5, Special, Constant, and uncheck all except Errors, click OK 6. Press Ctrl+- (control minus) and choose Entire row. 7. Clear the contents of the column where you put the formulas. Shane Devenshire "Sandeep Jangra" wrote: d'all, I hav a sheet of 5000 rows. I want to insert a blank row after next 100 rows. I don't want to insert row one by one bcz of time consuming. Plz give me any solution for that. |
#4
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Insert a row after next 100 rows
Shane gave you VBA code not a formula
See David McRitchie's site on "getting started" with VBA http://www.mvps.org/dmcritchie/excel/getstarted.htm best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "Sandeep Jangra" wrote in message ... HI, I used 2nd option just want to do once. I pasted that formula in cell A1. I have 1st row for heading. when I pressed ctrl++ and then select entire row. insert a blank row after 98 rows (not 100 rows) except heading row. And next blank rows are right. But i want to insert a blank after 100 data rows. Plz give me solution. "Shane Devenshire" wrote: Hi, If you just need to do this once you can do the steps manually, but if you might have to do it more than once, then the code below may be useful. The following code inserts a row at every 100th line. To use it for any other number of lines simply change the 100 in the first row of code to the number you want. To use it select a vacant range of cells in a single column corresponding to the height of the range you want to insert blank rows iinto and run the macro. Tools Macro Macros. Sub InsertRows() Selection = "=1/MOD(ROW(),100)" Selection = Selection.Value Selection.SpecialCells(xlCellTypeConstants, 16).EntireRow.Insert Selection.EntireColumn.Delete End Sub This code runs extremely fast. To add this code to a workbook: 1. Press Alt+F11 (opens the Visual Basic Editor) 2. Select your file in the Project - VBA Project window, top left (press Ctrl+R if you do not see it.) 3. Choose Insert Module 4. Copy the code above to the module on the right If this helps, please click the Yes button. The manual step a 1. Enter this formula on row 1 =1/MOD(ROW(),100) 2. Copy it down 5000 rows 3. Select all the formulas and copy them 4. With them selected choose Edit, Paste Special, Values 5. Press F5, Special, Constant, and uncheck all except Errors, click OK 6. Press Ctrl+- (control minus) and choose Entire row. 7. Clear the contents of the column where you put the formulas. Shane Devenshire "Sandeep Jangra" wrote: d'all, I hav a sheet of 5000 rows. I want to insert a blank row after next 100 rows. I don't want to insert row one by one bcz of time consuming. Plz give me any solution for that. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
insert rows in a worksheet that do not change adjoining rows | Excel Discussion (Misc queries) | |||
How do i insert blank rows between data that is thousands of rows | Excel Discussion (Misc queries) | |||
Insert rows: Formats & formulas extended to additonal rows | Excel Worksheet Functions | |||
How do I insert blank rows between rows in completed worksheet? | Excel Discussion (Misc queries) | |||
How do i insert of spacer rows between rows in large spreadsheets | Excel Discussion (Misc queries) |