PC Review


Reply
Thread Tools Rate Thread

adding rows to spreadsheet

 
 
janets
Guest
Posts: n/a
 
      19th Oct 2008
How can I automatically enter a blank row every 6 rows in excel. doing it
manually is too time consuming because the spreadsheet is so large.
 
Reply With Quote
 
 
 
 
Don Guillett
Guest
Posts: n/a
 
      19th Oct 2008
sub addblankrowevery6()
For i = Cells(Rows.Count, "a").End(xlUp).Row To 5 Step -6
Rows(i).Insert
Next i
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(E-Mail Removed)
"janets" <(E-Mail Removed)> wrote in message
news:AC988023-D6A7-4FC3-87AF-(E-Mail Removed)...
> How can I automatically enter a blank row every 6 rows in excel. doing it
> manually is too time consuming because the spreadsheet is so large.


 
Reply With Quote
 
ShaneDevenshire
Guest
Posts: n/a
 
      19th Oct 2008
Hi,

1. You can do this manually as follows in an empty column enter the formula
=1/MOD(ROW(),6)
and copy it down as far as necessary. The rows with DIV/0 will be the ones
that Excel will Insert, so if they are on the wrong 6th row modify the
formula by subtracting for example
=1/(MOD(ROW()-1,6)) will put the DIV/0 on rows 1, 7, ... rather than 6, 12,
18...
2. Select the cells an copy and then choose Edit, Paste Special, Values
3. Press F5, Special, Constants and uncheck all but Errors, click OK
4. Press Ctrl + (Ctrl and the Plus key) and respond Entire Row.
5. Select the column where the formulas are and clear it.

The VBA code to do this is given below. To run this select a blank column
range, such as B1:B1000 and run the macro.

This macro will run about 50-100 times faster than a standard For Loop or Do
Loop macro.

Sub InsertRows()
Selection = "=1/MOD(ROW(),6)"
Selection = Selection.Value
Selection.SpecialCells(xlCellTypeConstants, 16).EntireRow.Insert
Selection.EntireColumn.Delete
End Sub


If this helps, please click the Yes button.
--
Thanks,
Shane Devenshire


"janets" wrote:

> How can I automatically enter a blank row every 6 rows in excel. doing it
> manually is too time consuming because the spreadsheet is so large.

 
Reply With Quote
 
janets
Guest
Posts: n/a
 
      20th Oct 2008
Don:

Thanks for taking the time to answer, but where do I enter this code?
and do I need to tell the subroutine how large the spreadsheet is so that
it knows when to stop?

"Don Guillett" wrote:

> sub addblankrowevery6()
> For i = Cells(Rows.Count, "a").End(xlUp).Row To 5 Step -6
> Rows(i).Insert
> Next i
> End Sub
>
> --
> Don Guillett
> Microsoft MVP Excel
> SalesAid Software
> (E-Mail Removed)
> "janets" <(E-Mail Removed)> wrote in message
> news:AC988023-D6A7-4FC3-87AF-(E-Mail Removed)...
> > How can I automatically enter a blank row every 6 rows in excel. doing it
> > manually is too time consuming because the spreadsheet is so large.

>
>

 
Reply With Quote
 
Don Guillett
Guest
Posts: n/a
 
      20th Oct 2008

Just copy/paste into a module and fire it. You don't have to tell it how
large. Assumes column A.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(E-Mail Removed)
"janets" <(E-Mail Removed)> wrote in message
news:C0070F67-5731-4029-98F2-(E-Mail Removed)...
> Don:
>
> Thanks for taking the time to answer, but where do I enter this code?
> and do I need to tell the subroutine how large the spreadsheet is so that
> it knows when to stop?
>
> "Don Guillett" wrote:
>
>> sub addblankrowevery6()
>> For i = Cells(Rows.Count, "a").End(xlUp).Row To 5 Step -6
>> Rows(i).Insert
>> Next i
>> End Sub
>>
>> --
>> Don Guillett
>> Microsoft MVP Excel
>> SalesAid Software
>> (E-Mail Removed)
>> "janets" <(E-Mail Removed)> wrote in message
>> news:AC988023-D6A7-4FC3-87AF-(E-Mail Removed)...
>> > How can I automatically enter a blank row every 6 rows in excel. doing
>> > it
>> > manually is too time consuming because the spreadsheet is so large.

>>
>>


 
Reply With Quote
 
 
 
Reply

Thread Tools
Rate This Thread
Rate This Thread:

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

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Adding new rows to an Excel spreadsheet =?Utf-8?B?bmlsZXRyYWRlcg==?= Microsoft Excel Charting 0 21st Oct 2007 10:51 PM
Adding five new rows every 40 rows in a spreadsheet? =?Utf-8?B?T2x6a2k=?= Microsoft Excel Misc 8 18th May 2007 02:14 AM
Sum only adding rows on page instead of entire spreadsheet - help =?Utf-8?B?U2FyYWg=?= Microsoft Excel Misc 1 15th Feb 2006 11:46 PM
adding rows to spreadsheet after macro is written =?Utf-8?B?aWFtbjk0?= Microsoft Excel Programming 1 24th Apr 2005 07:54 PM
Adding new rows to spreadsheet Roy Microsoft Excel Programming 4 19th Dec 2003 12:01 AM


Features
 

Advertising
 

Newsgroups
 


All times are GMT +1. The time now is 12:19 AM.