need to input alternate blank rows in spreadsheet

Need to input alternate blank rows into imported spreadsheet.  Currently has 
24000 lines of data and would like to find a way of not having to manually 
input these blank rows! help!  Also if this is possible will I be able to 
format row height at the same time? 
0
Utf
11/30/2009 10:28:01 AM
excel.worksheet.functions 4936 articles. 2 followers. Follow

5 Replies
587 Views

Similar Articles

[PageSpeed] 31

Hi,

In a blank column, insert numbers from 1 to 24,000 I.e. if data is from 
A2:F24001, then in G2:G24001, enter 1 to 24000.  Now copy G2:G24001 and 
paste in G24002.  Thereafter you can sort the numbers in column G in 
ascending order.  This will insert one blank row after each row.

-- 
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"Confused from Hertford" <Confused from Hertford@discussions.microsoft.com> 
wrote in message news:E29222A2-25A1-464C-A913-A8F0AAE92C40@microsoft.com...
> Need to input alternate blank rows into imported spreadsheet.  Currently 
> has
> 24000 lines of data and would like to find a way of not having to manually
> input these blank rows! help!  Also if this is possible will I be able to
> format row height at the same time? 

0
Ashish
11/30/2009 10:40:17 AM
--Insert a temporary column say (ColA)
--In cell A1 enter 1, in A2 enter A2..Autofill upto 24000
--Copy A1:A24000 to A24001:A48000
--Now sort ColA ascending
--Delete ColA

If this post helps click Yes
---------------
Jacob Skaria


"Confused from Hertford" wrote:

> Need to input alternate blank rows into imported spreadsheet.  Currently has 
> 24000 lines of data and would like to find a way of not having to manually 
> input these blank rows! help!  Also if this is possible will I be able to 
> format row height at the same time? 
0
Utf
11/30/2009 10:43:01 AM
This assumes that column A is used in your data.  Run this small macro:

Sub RowInserter()
Set r = Cells(1, 1)

n = Cells(Rows.Count, 1).End(xlUp).Row
For i = n To 1 Step -1
Cells(i, 1).EntireRow.Insert
Next

End Sub

-- 
Gary''s Student - gsnu200909


"Confused from Hertford" wrote:

> Need to input alternate blank rows into imported spreadsheet.  Currently has 
> 24000 lines of data and would like to find a way of not having to manually 
> input these blank rows! help!  Also if this is possible will I be able to 
> format row height at the same time? 
0
Utf
11/30/2009 10:48:02 AM
Fill a simple sequence 1, 2, 3, 4 etc in a blank column next to your
data. Obviously, this will go down to row 24000. Then copy the numbers
in this sequence and paste them directly below, so that you now have
1- 24000 in those rows and 1 - 24000 in rows up to 48000.

Then select all that data down to row 48000 and sort using the helper
column as your sort field. Then you can delete the helper column.

Hope this helps.

Pete

On Nov 30, 10:28=A0am, Confused from Hertford <Confused from
Hertf...@discussions.microsoft.com> wrote:
> Need to input alternate blank rows into imported spreadsheet. =A0Currentl=
y has
> 24000 lines of data and would like to find a way of not having to manuall=
y
> input these blank rows! help! =A0Also if this is possible will I be able =
to
> format row height at the same time?

0
Pete_UK
11/30/2009 10:48:55 AM
Why do you need alternate blank rows?

This will make it very difficult to sort, filter, copy and other stuff.

If for appearance only, maybe just increase the row height to double?


Gord Dibben  MS Excel MVP

On Mon, 30 Nov 2009 02:28:01 -0800, Confused from Hertford <Confused from
Hertford@discussions.microsoft.com> wrote:

>Need to input alternate blank rows into imported spreadsheet.  Currently has 
>24000 lines of data and would like to find a way of not having to manually 
>input these blank rows! help!  Also if this is possible will I be able to 
>format row height at the same time? 

0
Gord
11/30/2009 5:26:12 PM
Reply:

Similar Artilces:

Choosing Report Format-Need advice
I asked this similar question a few days ago but received no response. Is there a way to change the properties when you’re using an AutoFormat for a report? I tried unsuccessfully to change the height of my alternating background when I used an AutoFormat (I’m using Access 2007). Is this the “rule”? I’m about to create several end-of-the-month reports that I plan to pull each month and thought I’d ask before I started. I’m also welcome to any suggestions about AutoFormat vs. designing your own report. Thank you for your input. AccessKay wrote: >I asked this simila...

Remove "Subfilter" Icon Needed
Greetings. I am using MS Excel 2007 (beta), and it's a peach, to say the least. I especially like the improved filtering functions. There is one thing, however, that I hope you will include in the final version scheduled to come out next year. When I filter the contents of a particular cell (right mouse click, filter by selected cell's value, etc.), and then in a different column filter on the contents of another cell within this first filter, I get another smaller "subfilter," of course. After doing something to the contents in this subfilter (filling in a blank, ...

Compuserve and Oulook replies blanked out
I use compuserve as my mail server. Up until now it worked well with Outlook. Since last week, I cannot see replies in my emails in Oulook, but they come fine at Compuserve. Replies start with my own email, but the one from the replier is blenked out. I wanted to know if anybody had a clue. My own email is germanaltgelt@cs.com Thank you German is it all messages or just some messages? What email program are your correspondents using? -- Diane Poremsky [MVP - Outlook] Author, Teach Yourself Outlook 2003 in 24 Hours Coauthor, OneNote 2003 for Windows (Visual QuickStart Guide) Outlo...

adding months to an inputted date
I need a function that will take a date that a user has typed in a different cell and will then add two months to the date. For instance, if I type "2/12/05" in B1, then I want C2 to be: "4/12/05". Thank you for any help that you may be able to give. Logan =DATE(YEAR(B1),MONTH(B1)+2,DAY(B1)) however what do you want the date to be in C2 if B1 is 01/30/05? Regards, Peo Sjoblom "BLW" wrote: > I need a function that will take a date that a user has typed in a different > cell and will then add two months to the date. For instance, if I type >...

Delete rows with true or false
Hi, I want to delete all the rows that contain the word true or false. How can I do this? Thanks Diana wrote: > Hi, > > I want to delete all the rows that contain the word true or false. How > can I do this? Can you sort on the column containing true or false, then select all the rows in one group and delete them? Or does it need to be automatic? -- Jordon If the data is in one column, you could apply data|filter|autofilter (in xl2003 menus) to that column. Show the rows that contain either True or False and delete the visible rows. Then remove the au...

How do you lock rows for scrolling ?
Hello all, I'm new to Excel and using Excel 2002. I can do basic functions and basic sorting, but that's about it at this point. I've seen some Excel spreadsheet that lock a few rows. Then when you scroll up only the rows below the locked rows actually scroll. For example if you have names in row 4 and all the details for each of those names in rows 5-200, how can you lock row 4 to always stay visible on your screen, even when scrolling down to view the rows in the 100's? I hope explained this well enough to get some help...lol. THANKS in advance !!! "FISH&q...

Need help grouping charts
I have identical charts which occur on multiple sheets. Is there any wa to group them for editing? When I group the sheets themselves, it seems to deactivate the charts I tried doing it with a macro as an alternative and then flipping th sheets one by one and keying the macro, but I get a message box with: "Run-time error 1004", "Unable to get the ChartObjects property of th worksheet class". Does anyone have any ideas -- Izz ----------------------------------------------------------------------- Izzy's Profile: http://www.excelforum.com/member.php?action=getin...

Hiding Rows with False
I've got a bunch of rows of data that I want to copy and put somewhere else, but many of the rows have "false" in them. They don't show anything and just look blank, but they leave the row blank and take up a lot of space. It looks something like this: tom 47 Mary 36 Bob 18 Dan 29 Ed 44 Is there a way to truncate the list by hiding the rows that return a false value and just making a normal list like the following? tom 47 mary 36 bob 18 dan 29 ed 44 You can apply Autofilter to one of the columns and just select Non- blanks from the filter pull-down (or, if ...

DB Mail is is not working. Need help please.
I have try stop and start the db mail by executing the SPs below but no luck. Does anyone has seem the error below and how to fix it. Thanks in advance for your advise/help. --steps USE msdb; go EXECute sysmail_stop_sp; go EXECute sysmail_start_sp; go EXECute sysmail_help_queue_sp @queue_type = 'Mail'; go queue_type length state last_empty_rowset_time last_activated_time ---------- ----------- ---------------------------------------------------------------- ----------------------- ------------...

Higlihting entire rows or lines?
Hi I was wondering is there any way of highlighting entire lines or rows in word? If you are talking about text lines, clicking next to the line in the left margin will select it. Similarly, you can click next to a table row to select it. (The mouse pointer will look like an arrow.) -- Stefan Blom Microsoft Word MVP "D Shah" <D Shah@discussions.microsoft.com> wrote in message news:59141C31-9ABF-4A90-9908-C6E9ACBE8613@microsoft.com... > Hi I was wondering is there any way of highlighting entire lines or rows > in > word? Do you mean as ...

Need to convert point on screen to various screen resolutions
Let's say you click on a button on your screen at 1000,2500 TWIPS and your resolution is 800 X 600. Now you change your screen resolution to 1024 X 768 and you need to click on the same button in it's new location on the screen. Who's 100 times smarter than I am and can do some tricky math that will tell me the TWIPS to find that button? I'd ned to do the same calculation for other screen resolutions like 640 X 480 etc. http://www.applecore99.com/api/api012.asp -- Regards, Tom Ogilvy "Donna YaWanna" <diy@mdahospital.com> wrote in message news:%23HMLEWW2...

Help need for .net assembly in Workflow manager
A simple code (given below) creates a task thro' .net assembly . I invoked this code via console application & found that it is running fine. It is creating task in CRM successfully. The problem is : when I included it in Work flow manager, it displays that "task completed" in workflow monitor but it is not creating a task in CRM . using System; using System.IO; using TaskCreation.CRMWeb; namespace TaskCreation { /// <summary> /// Summary description for Class1. /// </summary> public class Class1 { public Class1() { // // TODO: Add construc...

Need Help
Query: Say i have multiple sheets dated 04/01/10 to 04/30/10, each contain names, say A, B, C, D, etc... but these name does not appear in the same order in each sheet. Now data is in time format in front of A, B, C, D, etc. Eg. in sheet 04/01/10 A1 B1 A 1:00:00 D 1:53:00 C 2:45:00 like this data in other sheet till 04/30/10. Now in a new sheet i want this data to be collated in time format in front of the individual sorted in Alphabetical order. Also it should give me the Sum of the data. Is it possible and how? Thanks in advance for the help!...

corrupted PST file
Hi, I have Outlook 98, running on XP, on a PII 400Mghz Dell desktop. My PST file is about 1.95GB. It has been running extremely slow, and has had to be restarted a bunch of times in the past 2 weeks. Yesterday, it gave an error that the PST file was corrupted, and that PST file need to have the Inbox Repair Tool run on the file, and then the system restarted. I ran the Inbox Repair Tool on the PST, and it did not work. I am totally unable to use the file, which has all my critical contacts and emails. If someone could help out, I would be MOST grateful.... I pulling my friggin ...

Do you need MS Access to query on an Access table?
I'm asking these questions because I have looked at a lot of stuff in the discussion groups and still confused. I am fairly good at Excel programming but haven't been able to get Excel and Access to talk. First, I have Office 2003 Professional at work and Office 2003 without Access at home. I want to work on developing Excel programming at home which will get data from Access to Excel with either MS Query or with programming. Is it possible to just have the .mdb files on the home computer for Excel to work with, or do I need the Access program too? The info I want ...

Spreadsheet macro stopped working!
Hi, I recently made an Excel spreadsheet for keeping track of my golf score. The spreadsheet is quite simple. It consists of 4 sheets: Scorecard, statistics, database and equations (for calculating some specific). I insert informations about the golf course I'm playing and how my score was etc. I then hit a button "Save scorecard" that runs a macro. The macro inserts specifik values from the scorecard into the database sheet (which works like a charm). The macro also updates the "Statistics" sheet with information about stroke number and points for ...

bug in Excel 2003; need to install Excel 2002
I have Office 2003, and I found a bug in Excel 2003. The linear regression results using Excel 2003 differs with that of Excel 2002 for some cases. How do I can uninstall Excel 2003 (and install Excel 2002) without affecting the other Office apps (like Word 2003 and Powerpt2003)? Hi Excel 2003 has solved some bugs/errors with statsitical functions. So I would guess the 'new' result are more correct than the old ones -- Regards Frank Kabel Frankfurt, Germany "Speqter" <Speqter@discussions.microsoft.com> schrieb im Newsbeitrag news:451EE055-7023-4C3B-B816-829BA9FC6E...

multiplication in an Excell Spreadsheet
I am having difficulty in multiplying 285 units by 5.960 p. per unit. Can anyone help me solve this problem please? With 285 in cell A1 and 5.96 in cell A2, in cell A3 put =A1*A2 -- Regards Ken....................... Microsoft MVP - Excel Sys Spec - Win XP Pro / XL 97/00/02/03 ---------------------------------------------------------------------------- It's easier to beg forgiveness than ask permission :-) ---------------------------------------------------------------------------- "KiaOra" <KiaOra@discussions.microsoft.com...

average of non blank cells
i have read somewhere that it is possible to take an average of non blank cells....is this correct....if so how!! thanks in advance Robert. ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~View and post usenet messages directly from http://www.ExcelForum.com/ "robert_woodie" <robert_woodie.wgt6m@excelforum-nospam.com> wrote in message news:robert_woodie.wgt6m@excelforum-nospam.com... > > i have read somewhere that it is possible to take an average of non > blank cells....is this correct....if so how!! > > th...

How do I keep a formula from changing if a row is added or deleted
I have a formulas referencing cells in another worksheet. When rows are added or deleted from the worksheet the formulas are either changed to skip the new row or receive an #REF! error when a row is deleted. Regardless of what happens in the worksheet I want the formulas to remain the same. Hi see your other post -- Regards Frank Kabel Frankfurt, Germany cwolt wrote: > I have a formulas referencing cells in another worksheet. When rows > are added or deleted from the worksheet the formulas are either > changed to skip the new row or receive an #REF! error when a row is >...

All my emails are blank
Hi, After (or so it seems) installing msn messenger, all my emails on a pop3 server do not open - outlook reiceves them from the server but they are all blank (with a busy mouse pointer when you hover above them)this isnt only new emails but all the emails on my hard drive!! help! Faz ...

Need help in generating a report.
I have a spreadsheet (Sheet1) that summarizes all of my contracts. Column A contains the name of my clients for whom I do several jobs for. Column B contains the name of the contract. Column C contains a flag to indicate the job has been completed. Column F contains the profit/loss amount for that job. I only have a handful of clients but several contracts with each. On Sheet 2, I would like to have a report that sorts all of my contracts by clients, along with the name, amount each one made or lost, and then have a total for each client. The report should look something similar to this.....

NEED HELP/SQL UPGRADE/PLEASE!
Tried to upgrade to 2.0 last night, and am running into a major difficulty getting SO Admin to talk to SQL Server 2005. My error message is "SQL Server does not exist or access denied. Connection open (Connect())" I have been on the phone with Microsoft Tech Support for most of the day (and we have been running the store without a computer, which is no mean feat). Have installed and removed SQL 2005 several times, checked to make sure everything is running and started, that everything that is supposed to be is enabled.....any insights out there? By the way, it would be a goo...

Alternate grey/red shading with subtitles getting shaded differently (Word table)
Dear Experts: I got a telephone list/table with the following make-up: - Each name and corresponding phone number has got its own row. - The letters A-Z also have their own rows and are formatted bold - First name, last name is entered in one table cell - Phone numbers are entered in the table cells next to the "name" cell With any number rows of the table SELECTED would like the table to acquire the following FORMATTING: Rows A, B,C to Z (formatted bold) should have a grey shaded fill (RGB value 133, 133, 133) Rows immediately following these subtitles (A-Z) should...

Macro help needed #3
I'm learning macros at the moment and thought I'd venture to write one which I needed myself but I could do with some help, since I spent hours today trying everything that occured to me and searching the internet when I should be studying since I've an exam on macros tomorrow. I import an list of people from the internet by using a web query in excel (labelled provisions) I selected a group using the first letter of the last name and used record macro to copy the selection and paste the values into another worksheet labelled with that letter, eg labelled A. At the end of the m...