A Microsoft Excel forum. ExcelBanter

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » ExcelBanter forum » Excel Newsgroups » New Users to Excel
Site Map Home Register Authors List Search Today's Posts Mark Forums Read Web Partners

Insert a row after next 100 rows



 
 
Thread Tools Display Modes
  #1  
Old November 12th 08, 06:24 AM posted to microsoft.public.excel.newusers
Sandeep Jangra[_2_]
external usenet poster
 
Posts: 17
Default 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.
Ads
  #2  
Old November 12th 08, 07:08 AM posted to microsoft.public.excel.newusers
Shane Devenshire[_2_]
external usenet poster
 
Posts: 3,346
Default 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  
Old November 12th 08, 07:46 AM posted to microsoft.public.excel.newusers
Sandeep Jangra[_2_]
external usenet poster
 
Posts: 17
Default 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  
Old November 12th 08, 01:52 PM posted to microsoft.public.excel.newusers
Bernard Liengme
external usenet poster
 
Posts: 4,393
Default 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
news
> 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.



 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
insert rows in a worksheet that do not change adjoining rows craigandmel Excel Discussion (Misc queries) 2 April 29th 08 10:26 PM
How do i insert blank rows between data that is thousands of rows paul.eatwell Excel Discussion (Misc queries) 5 April 14th 08 10:49 PM
Insert rows: Formats & formulas extended to additonal rows Twishlist Excel Worksheet Functions 0 October 22nd 07 04:23 AM
How do I insert blank rows between rows in completed worksheet? bblue1978 Excel Discussion (Misc queries) 1 October 26th 06 07:02 PM
How do i insert of spacer rows between rows in large spreadsheets laurel Excel Discussion (Misc queries) 0 April 24th 06 01:38 PM


All times are GMT +1. The time now is 01:38 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2014, Jelsoft Enterprises Ltd.
Copyright 2004-2014 ExcelBanter.
The comments are property of their posters.