Insert blank rows

Rather than using the control button to select every other row, which can be 
very time consuming when you have more than 100 rows, I am trying to figure 
out how to auto-insert blank rows between pre-populated rows of information.
0
Utf
12/18/2009 9:19:02 PM
excel.misc 78881 articles. 5 followers. Follow

2 Replies
964 Views

Similar Articles

[PageSpeed] 40

Deb,

Try this macro. ALT+F11 to open VB editor. Right click 'ThisWorkbook' and 
insert module and paste the code in on the right . Change the sheet name to 
your sheet and run the code

Sub insert_rows()
Dim ws As Worksheet, x as long
Set ws = Sheets("Sheet3")
lastrow = ws.Cells(Cells.Rows.Count, "A").End(xlUp).Row
For x = lastrow To 2 Step -1
    ws.Rows(x).EntireRow.Insert
Next
End Sub


Mike

"Deb" wrote:

> Rather than using the control button to select every other row, which can be 
> very time consuming when you have more than 100 rows, I am trying to figure 
> out how to auto-insert blank rows between pre-populated rows of information.
0
Utf
12/18/2009 9:31:01 PM
Blank rows within your data range will give you problems with copy, paste,
sort, filter and probably other functions.

Do you really need those blank rows?

If just for appearance, doubling the row heights may help you.


Gord Dibben  MS Excel MVP

On Fri, 18 Dec 2009 13:19:02 -0800, Deb <Deb@discussions.microsoft.com>
wrote:

>Rather than using the control button to select every other row, which can be 
>very time consuming when you have more than 100 rows, I am trying to figure 
>out how to auto-insert blank rows between pre-populated rows of information.

0
Gord
12/19/2009 8:14:21 PM
Reply:

Similar Artilces:

counting rows
I have 100 rows in a column A which are numbered 1 -100 in numical order. After I filter a particular item in column B which results in 20 rows (and these 20 item were scattered throughout the column), I would like column A to show 1 - 20 in numerical order. Try this... Assume the data in column B is in the range B2:B1010 with B1 being the header. Enter this formula in A2 and copy down to A101: =SUBTOTAL(3,B$2:B2) -- Biff Microsoft Excel MVP "Darryl" <Darryl@discussions.microsoft.com> wrote in message news:89190AE3-6D26-4F2C-9C2D-51EB5312CB1D@mi...

Blank password
Hi I have installed office 2000 on a brand new xp pro xp1 machine. The problem is that password does not stick in outlook internet account. I have to re-enter the password every time I send/receive email. The relevant registry entries are as follows; Windows Registry Editor Version 5.00 [HKEY_CURRENT_USER\Software\Microsoft\Office\Outlook\OMI Account Manager\Accounts\00000001] "Account Name"="Operations" "POP3 Server"="192.168.1.55" "POP3 User Name"="Operations" "POP3 Skip Account"=dword:00000000 "POP3 Prompt for Pas...

SELECT/INSERT statements via the RS utility
I'm trying to build a process that will copy reporting services reports using the rs utility. I'd like this process to be able to SELECT and INSERT records from a database. Does anyone have a sample of some code that will show how to issue a SELECT or INSERT statement via a RSS script passed to the RS utility. ...

about:blank
When reading emails, I can no longer double click on a link and see the web page that the email is providing a link to. When I double click on a link to a web page that is in an email message, Internet Explorer starts up, then shows an empty page, with the words "about:blank" in the URL. If internet explorer is already running, an empty page comes up, with "outbind:/2-00....." in IE's URL. This used to work properly, so I can't blame it on improperly formatted incoming emails. I've done a "view source" on one of the problem emails, and on an email w...

search a row for a word and if found
I have 4 columns (C to F) I need to search row by row for a particular word. If the word is found in a row, I need to return 1 in Column G or 0 if not found. I've tried various iterations of IF\SEARCH\MATCH and so on. The four columns contain address lines, i.e. a mixture of text and numbers and the word can appear in any of the columns, at any place in the string. I'd prefer if it weren't case sensitive but it's not imperative. Many thanks in anticipation! Try the below =IF(COUNTIF(C4:F4,"*findword*"),1,0) 'with the query word in cell A1 =IF(...

sum and minus columns and rows at the same time
How do I use the minus feature to subtract numbers in a row and then keep a running total in the corresponding column? I have tried everything I know and I can't seem to get it done right. PLEASE HELP!!!!!!!!!!!!!!!!!! ...

Can't insert a new row
I have about 20 rows of data and I'm trying to insert a row of data before line 6. I highlight cell 6, click Insert /Rows and then I get an error, "To prevent the possible loss of data, Microsoft Excel cannot shift nonblank cells off the work sheet." It goes on with some more garbage, but it's already obvious that Microsoft doesn't know what it's talking about. How do I get this insert to work? Thanks! -- skywaytraveler ------------------------------------------------------------------------ skywaytraveler's Profile: http://www.excelforum.com/member.php?ac...

Error in Project Purchasing: Cannot insert Value
When I try to post project purchase shipment, I get the following message: Cannot insert the value Null into column PAQuantityPcntCompleted table CWR.dbo.PA01301. Column does not allow Nulls. I am on Great Plains 8.0 SP2. I ran the PA Conversion Utilities so that all of the stored procedures are installed correctly to be on the safe side. I am still getting this error. Please help. HI jack, Never seen this specifically in Project A/c or for that table BUT have seen it in other modules. Usually it is a result of the table strucuture/properties being changed in the database. Does thi...

Exchange 2003/ 2007 emails coming in blank, but text appears if forwarded
We have two email servers, one that is Exchange 2003 and another which have just 30 users on that is on Exchange 2007 (we hope to migrate the rest of the 2003 users on to it). These are backend servers, but all emails within our company are delivered by one of two hub-client servers (both of which are Exchange 2007). We have this weird problem where occasionally people get messages sent to them which appear entirely blank, but if they then forward them, the text becomes visible. It appears that something is playing with the formatting of emails, and I am not sure what. Sometimes we have found ...

Insert button on keyboard
Is there any way to stop this button from functioning? I'm sure there are many people who do need the ability to type over what has been written (if that needs to be done, just select the text you want to go, and press Delete). Its just a constant annoyance finding you've accidentally hit the Insert key and then typed over a few characters, having to undo, press Insert again, continue, etc. Thanks in advance, Alain "Alain Dekker" <alain.dekker@NO.SPAM.loma.com> wrote in message news:uTdBd1i2KHA.1016@TK2MSFTNGP02.phx.gbl... > Is there any w...

Sum of row excluding hidden columns
Hello, I am trying to take the sum of say B5:AZ5, excluding hidden columns. The SUBTOTAL function, using funciton #109 would allow me to do B5:B50, but not the B5:AZ5 (it allows for taking the sum of a column, excluding hidden rows, but not the other way around). Is there a *decent* alternative? I have thus-far found a macro that can handle this, but a re-calculation is needed on the user's part if a column is hidden/unhidden. I also found a cludgey (but nonetheless interesting) way posted by Biff on October 12, 2006, but am looking for a (a) more elegant solution [if it exsits...

Excel To Access: Transfer multiple rows from excel at the same tim
Hi All, How can I transfer a Bock of data to Access from excel by clicking a "Submit" button? eg: I have designed a "Submit" button on the excel sheet that exports all of the student data into access, but data is populated in a single row...AND I want to insert all this data in access in seperate rows. Here is what My excel table looks like: Student_ID Subjects Grades 123456 Eng A 123456 Hist B 123456 Math B+ 123456 Bio B- So, once we click "Submit" I want the dat...

Can you change something in Excel to automatically insert an equa.
I'm generally used to Lotus 1-2-3 where for a calculation I can just enter 1+2 and have it display 3. In Excel I always have to enter =1+2. If you don't enter the = it will just display 1+2 after you hit enter. Is there something in the settings you can change so that it will automatically enter the = sign? The only other option is to enter "+1+2" which will return 3 This is an Excel requirement. A tradeoff from Lotus is if you WANT the cell to display "1+2" in Lotus, you must enter the apostrophe " '1+2 ", in Excel, you don't. HTH ...

blank sender name & topic
Please help, i received unsolicited email which have blank sender name and topics. izzit a outlook bug or virus. the following info is retrieve from the email. Microsoft Mail Internet Headers Version 2.0 Received: from RAYANPROXY ([195.146.59.57]) by seemail.seem.com.my with Microsoft SMTPSVC(5.0.2195.6713); Sun, 5 Sep 2004 10:06:19 +0800 X-Message-Info: 07FP02HCcilcp22N8ggZXPrBGZ2fC7lyXMscE3X667 Received: (from moorish@195.146.59.57) by eaten6[1 thanks * best regards, Yhng It has nothing to do with Outlook, it is simply spam. Get SpamBayes and train it - soon you will be a happy...

Importing a text file w/ more than 65,536 rows
I'm trying to download a text file which exceeds the number of rows on an Excel worksheet. The help menus for Excel 5.0, 7.0, and '97 specify a way to use the Text Import Wizard to import the file to multiple worksheets. However, I have Excel 2000, and cannot locate any instructions about how to download to multiple worksheets. Anyone have any suggestions? I'd use my favorite text editor and split the text file into smaller pieces. Then import them separately. Or you could use a macro that imports each line: http://support.microsoft.com/default.aspx?scid=kb;EN-US;q120596 ...

Autofit row height in only some rows
Is it possible to change this macro to only autofit the row height on rows 254-332 in the workbook? I am using this macro: Option Explicit Private Sub Workbook_SheetCalculate(ByVal Sh As Object) Application.EnableEvents = False Sh.Rows.AutoFit Sh.Rows("1.33").AutoFit Application.EnableEvents = True EndSub Thanks in advance for your help. VBA Code: -------------------- Option Explicit Private Sub Workbook_SheetCalculate(ByVal Sh As Object) Application.EnableEvents = False Sh.Rows("254:332").AutoFit Application.EnableEvents = True ...

MS Query and Row Range Names
We utilize MS queries in a spreadsheet that has range names for rows. Unfortunately, when the query returns a different # of records than before the query, it messes up the reference of the range names (i.e. if row 123 is named "Partner1" before the refresh of the query, "Partner1" will not move with the associated row if new records are inserted and/or deleted - NOR does it stay at row 123). Does anybody know what is going on and how to overcome this issue? Thank you. Correction to my original message, the range name does stay at row 123 (i.e. absolute). But I h...

Row/Column unhide shortcut keys not working in excel 2007
Specs: AMD 64 X2, Vista Business Edition, Office 2007. I searched through the groups, but I didn't come across an answer to my problem.. Do the shortcut keys for row/column unhide work in excel 2007? I'm used to excel 2002/03 where the command is CTRL+SHIFT+9 and CTRL+SHIFT+0. According to the excel 2007 help, the commands are the same, but they do not work for me (I can unhide by going to Cells- >Format-> Hide & unhide). Interestingly, Ctrl+9 and Ctrl+0 work fine for hiding rows/columns. On a (related?) note, in the following thread http://groups.google.com/group/microso...

transferring data in rows of one table to columns of another table
Hi All, We are working with FCC station data that puts some simple numeric data in one file, arranged in a number of consecutive rows for each station. The next station's data follows consecutively. Each group of rows that are common to a station share an index number, while the next group uses its own separate number. A separate file contains the main information fields in a table of separate rows, or records, along with a matching index number. We'd like to move the numeric data in a group of rows that share the index # for a station, to a series of new fields add...

VBA
How do i get VBA to insert a new column? Columns("G").Insert as simple as that -- __________________________________ HTH Bob "Tree*Rat" <tree.rat@googlemail.com> wrote in message news:Xns9B3D50C9D6557squirrelsaladgooglem@69.16.176.253... > How do i get VBA to insert a new column? A word of advice. The macro recorder can be a good learning device. Suggest you use it before asking questions you can answer yourself. -- Don Guillett Microsoft MVP Excel SalesAid Software dguillett1@austin.rr.com "Tree*Rat" <tree.rat@googlemail.com> wrote in...

Comb box won't update its value (remains blank)
I am using Access 2007 in 2002/2003 mode on Vista. I have added a combo box to a form using the wizard which looks up its values in a table. When I tried to test it just now, the drop down has the correct values, but the value I select does not appear in the unexpanded combo box. It just stays blank. The table is linked from another database. The form is one I am using as a dialog box with no control source. Can anyone help me figure out why the combo box won't update its value (or maybe its not displaying its value). Thanks. - Paul Schrum If your combo has more than one column,...

remove excess (unwanted) rows from the bottom of a speadsheet
I have some spreadsheets submitted to me that are upto 65000 rows, with only the first 4000 rows used. How can I remove these unwanted rows. Deleting only replaces teh rows with new blank rows. At the moment I am cutting / pasting in to a new worksheet and then re-establishing all the links. THere must be an easier way. You can't remove them, as the sheet is a fixed size. However, you can hide them. Select the rows below the data then click on Format | Rows | Hide then click OK. Hope this helps. Pete On Nov 26, 10:01=A0am, alun.re...@bamnuttall.co.uk <alun.re...@b...

insert dialog
A question regarding MS Visual C++ 6.0 and inserting a second dialog: Could someone tell me what additional steps to take in order to be able to add in a second dialog box after you have let the MFC Wizard create a working dialog project of lets say name of: FIRST? So far I have taken these steps and am getting error messages. steps: 1 In resources of FIRST clicked on insert dialog 2 New dialog gets ID of ID_Dialog1 3 Use wizard to Create new class CSecondDialog, with base class set to Cdialog 4 In the command button's code section that I want to have open the second dialog box I add the...

How set up a template/form & database that records variable rows .
Trying to set up a quote database that records muliptle records (will vary # of rows) depending on the # of products quoted. ...

Averages with blank cells and specified criteria
Hi, I am trying to develop a formula to have attendance reports and averages. I want to divide the groups into adults, youth and children. I have a column identifying the group each person is in (A Y or C). I have a worksheet for each program (wed, thurs, fri or sun) and i want to tally the totals on another sheet and then make an overall statistics sheet. I am using =SUMIF(Sunday!B:B,"A",Sunday!E:E) on the initial stats but it is coming up as an error. I am using =AVERAGE(IF('Initial Stats '!4:4>0, 'Initial Stats '!4:4,"")) I thought I had everyt...