Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
DiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWord
DirectoryUser Groups
Related Topics
Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ...

MS Office Forum / Excel / New Users / April 2007

Tip: Looking for answers? Try searching our database.

Need to Add a Blank Row between every Row in a >400 Row Spreadsheet

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
Mike - 10 Apr 2007 12:11 GMT
Hi, is there any way I can automate the process of of adding a blank row
between rows in a >400 row Excel 2003 spredsheet.?

Thanks,

Mike
Dave Peterson - 10 Apr 2007 12:38 GMT
I think that this is usually a bad idea.  It can mess up filters, graphs,
pivottables, ...

If you want to make it look double spaced, then increase the rowheight.

But you can do it a few ways.

One way (manual):
Insert a new helper column
put =row() in the top cell of that new column and drag down (>400 rows)
Then convert those formulas to values (edit|copy, edit|paste special|values)

Then copy those numbers to the rows directly beneath the >400 rows.

Now select the whole range (row 1 to > row 800).

Sort your data by that column.
Delete the helper column.

Another way is to use a macro.

This picks out a column that has data in it on the last row -- I used column A.

Option Explicit
Sub testme()
   Dim LastRow As Long
   Dim FirstRow As Long
   Dim iRow As Long
   
   With ActiveSheet
       FirstRow = 2
       LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
       
       For iRow = LastRow To FirstRow Step -1
           'to increase the rowheight to twice the height
           '.Rows(iRow).RowHeight = 2 * .Rows(iRow).RowHeight
           'to insert a new row
           .Rows(iRow).Insert
       Next iRow
   End With            
End Sub

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

> Hi, is there any way I can automate the process of of adding a blank row
> between rows in a >400 row Excel 2003 spredsheet.?
>
> Thanks,
>
> Mike

Signature

Dave Peterson

Mike - 11 Apr 2007 03:10 GMT
>I think that this is usually a bad idea.  It can mess up filters, graphs,
> pivottables, ...
>
> If you want to make it look double spaced, then increase the rowheight.
>
> But you can do it a few ways.

This is an inventory spreadsheet w/ no graphs, tables or anything else.
I'm going to do an eyball inventory and want to write changes in the blank
rows then when I return to
my office I'm add the data to the spreadsheet.  I'll try your suggestions.

Thanks

Mike

> One way (manual):
> Insert a new helper column
[quoted text clipped - 42 lines]
>>
>> Mike

Rate this thread:






 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.