How do you automatically put spaces between rows in a lengthly w.

I have a lengthly work sheet and I want to put spaces between every row--how 
do I do that?
0
marvaod (1)
3/18/2005 10:09:01 PM
excel.misc 78881 articles. 5 followers. Follow

2 Replies
679 Views

Similar Articles

[PageSpeed] 25

Automatically usually involves VBA coding.

Do you really need the inserted rows?  Perhaps you could just change the
height of the rows to look double-spaced?

Here is a macro to do the row inserting.

Sub InsertALTrows()
'David McRitchie,  misc  2001-06-30
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual        'pre XL97 xlManual
    Dim i As Integer
    For i = Selection(Selection.Count).Row To Selection(1).Row + 1 Step -1
        Rows(i).EntireRow.Insert
    'With Rows(i)
    ' .RowHeight = 24.25
    ' End With
    Next i
    Application.Calculation = xlCalculationAutomatic        'pre XL97
xlAutomatic
    Application.ScreenUpdating = True
End Sub


Gord Dibben Excel MVP

On Fri, 18 Mar 2005 14:09:01 -0800, "marvaod"
<marvaod@discussions.microsoft.com> wrote:

>I have a lengthly work sheet and I want to put spaces between every row--how 
>do I do that?

0
Gord
3/19/2005 12:10:49 AM
One way ..

Assume your source data is
in Sheet1, in cols A to C, data from row2 down

In Sheet2
--------
Put in A2:

=IF(MOD(ROWS($A$1:A1)-1,2)=1,"",OFFSET(Sheet1!$A$2,MOD(ROWS($A$1:A1)-1,2)+IN
T((ROWS($A$1:A1)-1)/2),COLUMNS($A$1:A1)-1))

Copy A2 across to C2, fill down until zeros appear, signalling exhaustion of
data from Sheet1

The above will return what you want
(insert alternating blank rows in-between the data from Sheet1)

Do a copy and paste special as values elsewhere as desired

--
Rgds
Max
xl 97
---
GMT+8,  1� 22' N  103� 45' E
xdemechanik <at>yahoo<dot>com
----
"marvaod" <marvaod@discussions.microsoft.com> wrote in message
news:79DF177C-FDEF-405D-9392-7209DBD8535E@microsoft.com...
> I have a lengthly work sheet and I want to put spaces between every
row--how
> do I do that?


0
demechanik (4694)
3/19/2005 12:28:25 AM
Reply:

Similar Artilces:

remove 1st 2 letters in each row
I have 30 rows and each row I need to delete the first 3 letters. ex UNE127 UNE50 so it will be 127 50 Can someone please tell me how I can do this without going in and deleting each row. Thanks A simple MID might suffice Assume your data in A2 down In B2: =MID(A2,4,99)+0 Copy down. The "+0" bit will convert the extracted text nums to real numbers. Success? hit the YES below -- Max Singapore --- "Cathy" wrote: > I have 30 rows and each row I need to delete the first 3 letters. > ex > UNE127 > UNE50 > &...

A program is trying to automatically send e-mail on your behalf.
I tried posting this in microsoft.public.outlook.general, but no nibbles. ------------------------------------------------------------------------------------------------------ Does anybody know how to turn the "A program is trying to automatically send e-mail on your behalf." prompte off? It pops when one of my MS Access apps is emailing one of it's reports to somebody. It happens on the Citrix server that one of my MS Apps is running on, but it does not happen when the app is running on my own PC. Consequently I'm hoping for some user-configurable setting that controls...

Money 2006 constantly faults with automatic updates
Whenever I use the Internet automatic updates, money faults out and wants to send a report to Microsoft. Also my account list shows most accounts with a status of 'Update In Progress' I've done a complete reinstall after removing it and power cycling my machine. I thought it may have been something with removing 2004 and installing 2006, but got same problem Has anyone else been having this problem? I really just want to go back to Money 2004. What's the error message? -- Glyn Simpson, Microsoft MVP - Money http://money.mvps.org Check http://money.mvps.org/faq f...

Automatic CC
Hello, Is there any way to create a template where a contact is automatically CC in the email. So I dont forget to cc them every time I send an email. Thanks ...

Automatic axis format
Hi, I'm need to make a automatic axis format within excel. The number of rows is depending of a imported file. No problem to get the max. number of rows. But to put this number into the chart ( category axis) is more problematic. I try VB but my knowledge is at dummy level ! Waiting for your reply............. Hi, Have a look at Jon's examples of dynamic charts. http://peltiertech.com/Excel/Charts/Dynamics.html Cheers Andy jos wrote: > Hi, > I'm need to make a automatic axis format within excel. > The number of rows is depending of a imported file. > No problem to g...

Column format to override row fomat in FrX
In FrX the row format overrides the column format for font styles eg. bold etc. it would be useful if it was possible to have the option for the column format to override the row fomat ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I Agree" in the message pane. http://www.microsoft.com/Businesss...

I open the MDB, the form runs automatically
Simple question I have an MDB file with 20 files and 7 forms in it. When I open it up, the form runs immediately an access goes into some sort of protected mode. I can't view the tables, queries etc. I can see the code by pressing Alt F11 but not the data. I created a blank db and imported the mdb so I could see the tables. But how do I open the original without it running the code? When I look at the code. I don't see anything that sets properties or invokes the code automatically. There are no macros in this, just the forms and associated code with them. Any ideas Thanks Colm ...

Automatic updates, automatically disabling itself
Automatic updates, automatically disabling itself. 2nd day now onecare popped up to tell me the updates turned themselves = off. I'm thinking what malware would want to turn the updates off ? Any idea what's going on here ? Did I AUTOMATICALLY install an update = that caused this ? <keepout@yahoo.com.invalid> wrote in message news:8l7mi5tkgp1gd15fkef71ll5mrui9ukmht@4ax.com... > Automatic updates, automatically disabling itself. > > 2nd day now onecare popped up to tell me the updates turned themselves > off. Drop OneCare and install Microsoft Sec...

Excel "Not Enough Available Memory or disk space
Version: 2004 Operating System: Mac OS X 10.4 (Tiger) Processor: Intel Hi, has anyone seen this message when trying to open an Excel File? "Microsoft Excel cannot open or save any more documents because there is not enough available memory or disk space." This happens on any excel documents of any size and only a logout seems to clear this up. The users have plenty of RAM (4gigs) so that's not the issue and there's plenty of disk space as well. Any help appreciated! "tjbud@officeformac.com" wrote: > Hi, has anyone seen this message when trying to open an Exce...

Find highest value in each row.
Hi, I have a problem trying to find the highest value for each row. I have a query, which adds up scores. Its based on a table something like this orgID Score1 Score2 Score3 Score4 Score5 Score6 1 10 1 5 2 11 20 2 1 4 2 12 11 24 1 10 7 15 12 1 2 and so on..... I have query that basicly adds up some of the scores into groups. So its like: orgID ScoreSet1:[Score1]+[Score2]+[Score3] ScoreSet2:[Score4]+[Score5]+ [Score6] 1 16 ...

My worksheet automatically adds a fill color when I enter text.
When I type text in a blank cell, the cell automatically is highlightes with the bright yellow fill. There is no conditional formatting on this workbook. Check your VBA editor. You might have an Event Macro which does what, usually, a CF does. Try to Increase the Macro Security to its maximum level in order to eliminate the Event Macro from running... You may, also consider to "rem" all the Macro commands by adding a Preceding apostrophe to each command. Micky > When I type text in a blank cell, the cell automatically is highlightes with > the bright yello...

How do I get an automatic equal sign to begin the formula bar?
How do I get an automatic equal sign to appear in the formula bar in Excel for Microsoft 2003? This happened for me in the 2000 version and I could just click on it with my mouse pointer, but now all that appears is the function sign with no equal sign after it. It makes my job a lot more time consuming to have to type in the equal sign while building formulas. Please help, anybody? The = icon disappeared in newer versions of excel (xl2002???). You can add an = icon Tools|customize|commands tab|Insert category (Scroll down the list until you find the = icon) And drag it to your fav...

Use a cell's value as row number for another cell in Excel
Excel XP: I'm trying to use the values in one row of cells (D1:F1) as the reference for the row value for a different series of cells (D2:F4). For example: Column A B C D E F Row 1 1000 30 170 1 2 3 2 1500 60 190 3 1200 20 175 I want to get the following cells to equal: D2=1000 (In other words: D2 = A1); D3=30 (In other words: F2 = B1); E2=1500 (In other words: E2 = A2); E3=60 (In other words: F2 = B2); F2=1200 (In other words: F2 = A3); F3=20 ...

Pinning a row
How do I keep the first row always visible when I scroll down? Click in A2, and then, <Window> <Freeze Panes> Here's an old post that might help explain: http://tinyurl.com/2qdzt -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Rick" <rhuldisch@hotmail.com> wrote in message news:93E01890-F83F-40CC-9F6A-F2D44EEF3D36@microsoft.com... > How do I keep the first row alway...

Having numbers automatically appear bold
Hi all, Had such great advise last time, thought I'd try again. I have a large range of cells where I will be inputing sales figures i the form of currency revenue. The range is about G-I and 1-400. Th sales data will be a comparisson of YTD 2001 - 2004 revenue results. What I hope to do is have any figure that is above $30,000 appea automatically in bold so as to stand out as a good client. I gues there is something in the IF function to do this but I'm stumped. Any ideas? Thanks again in advance. Saxte -- Message posted from http://www.ExcelForum.com select the range you wa...

Is it possible to automatically move data entered in columns into rows?
Is it possible to automatically move data entered in columns into rows? It seems like a simple thing to do but I can't find a function anywhere. Cheers, Richard Thorneycroft Husky Products If you enter something in ONE cell it is automatically in a row and a column. So, perhaps a bit more explanation is in order. -- Don Guillett SalesAid Software donaldb@281.com "Richard Thorneycroft" <dancecommander81@hotmail.com> wrote in message news:9e2f3f75.0411160750.7003c6ab@posting.google.com... > Is it possible to automatically move data entered in columns into rows? >...

Inserting Rows Problem
Hi there, I have a list of about 2000 rows. However, in between each of thes rows I want to insert a blank row that can be used for people to writ in once the list is printed off. Is there a quick way to do this? Kind regards Andre -- andyp16 ----------------------------------------------------------------------- andyp161's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1165 View this thread: http://www.excelforum.com/showthread.php?threadid=26611 If you insert rows it will affect sorting, etc. and be a real pain. Many posts here want to delete blank rows ...

Automatic Forward
How can I automatically forward messages received from a specific sender to another's e-mail person? Thanks, ...

CE6: How to Disable SIP popup automatically
Hi, I know SIP popup can be disabled by the following registry setting in CE5: [HKEY_CURRENT_USER\ControlPanel\Sip] "TurnOffAutoDeploy"=dword:1 But It doesn't work in CE6. How to disable SIP auto-Popup in CE6? Thanks. Eric The SIP Auto Deploy in CE is due to Windows Controls invoke SHSipPreference (exported by AYGSHELL.DLL). So the easiest way is to avoid including AGYSHELL (SYSGEN_AYGSHELL) But if excluding AYGSHELL is not an option, you may need to modify the SHSipPreference (PRIVATE\SHELL\SHELLPSL\HAVEAYGSHELL\shellpsl.cpp) to supress SIP s...

Automatically format charts with range of dates
What I am trying to do is I have 4 charts that are linked with data from another spreadsheet. They are basically week totals for my business and make a chart compared to say last year. What I want to do though is have an option on my report that I can enter in a specific date or a range of dates and have the charts automatically filter the new data in and change. Instead of having to go into the chart data and changing what series of data I want to have a chart for I want to be able to just enter eg. "April 12-April 18" and have the charts bring up that data to reflect the ...

Automatic Database Backup
Is it possible to right code to automatically backup a database? And if so how, or where, can I get information on this? Hi There are a few way to do this. I have windows scheuler doing this on all my databases (at 1 min past midnight each day). Open Scheduled Tasks (it's in the control panel). It's very simple and not likely to go wrong -- Wayne Manchester, England. "Ayo" wrote: > Is it possible to right code to automatically backup a database? And if so > how, or where, can I get information on this? Thanks Wayne. So I take it that, there is no way t...

Insert date automatically
I have a column with values such as: 3/25 3/24 3/29 3/21 3/21 3/21 And wish to automatically add todays date if the value of the cell is blank in the mm/dd format. I have used an =If(Column:Column="",TODAY()) in a column next to it and formatted it in mm/dd to get the desired result. But how can I take this value (todays date) and insert it back into the original column automatically and without changing the other valuse? As always thank you! Rick Hi, Rick: Here's one way. Step 1 is to make a backup of your original file so you have a fallback in case something goes ...

How to put tic marks in brochure for folding accurately?
I want to fold the brochure perfectly but I can't figure out how to put the marks in to show you where to line the paper up for folding. This is in Publisher 2003. ChristineC <ChristineC@discussions.microsoft.com> was very recently heard to utter: > I want to fold the brochure perfectly but I can't figure out how to > put the marks in to show you where to line the paper up for folding. What kind of folding is this brochure? A single centrefold (booklet fold), or a tri-fold or four-panel brochure? -- Ed Bennett - MVP Microsoft Publisher http://ed.mvps.org/ A tri...

Ignoring spaces
I need to collect part status information from 2 sources. The problem i that one of the sources is placing spaces before the part number. M worksheet is using column "D" for my part numbers. Source 1(MES) i entering a productsum in column "AB", Source 2(SCIM) is entering productsum in column "AC". This formula works great: =SUMPRODUCT(--('S:\Shared Machining\625\[8358.xls]rptSingleSection.as ilc=No&sec'!$D$2:$D$5000=D5),--('S:\Share Machining\625\[8358.xls]rptSingleSection.as ilc=No&sec'!$H$2:$H$5000="F"),'S:\Share Machini...

How do I restore fonts or 2 row tool bar in Excel 2003?
I'm a begginer and I'm doing homework on Excel 2003. I had been able to click on the toolbar on the far right and get toolbar options. Display toolbar on 1 row or two. Now I can only get one row and I need the second one because it has the fonts on it. Some one please help! Have a look at Tools>Customize then the Options tab and see how the checkbox for Standard and Formatting toolbars sharing one row is set. "50yearoldfreshman" wrote: > I'm a begginer and I'm doing homework on Excel 2003. I had been able to > click on the toolbar on the far righ...