Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Adding new rows in between rows...
I need to add a blank row in between each row and the length of the document
is seven pages; so, is there a way I can do this without having to add them individually?? If you can help me with this........thanks for saving my sanity!!! |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Adding new rows in between rows...
Sub insertrow()
lastRow = Range("A" & Rows.Count).End(xlUp).Row * 2 MsgBox lastRow For i = 1 To lastRow i = i + 1 Range("A" & i).EntireRow.Insert Next End Sub "MarshaMarsha" wrote: I need to add a blank row in between each row and the length of the document is seven pages; so, is there a way I can do this without having to add them individually?? If you can help me with this........thanks for saving my sanity!!! |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Adding new rows in between rows...
Thank you Mike!!
"Mike" wrote: Sub insertrow() lastRow = Range("A" & Rows.Count).End(xlUp).Row * 2 MsgBox lastRow For i = 1 To lastRow i = i + 1 Range("A" & i).EntireRow.Insert Next End Sub "MarshaMarsha" wrote: I need to add a blank row in between each row and the length of the document is seven pages; so, is there a way I can do this without having to add them individually?? If you can help me with this........thanks for saving my sanity!!! |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Adding new rows in between rows...
Hi
Try this macro Sub InsertRows() Dim r As Integer Dim TargetRange As Range Set TargetRange = Range("A1", Range("A1").End(xlDown)) rCount = TargetRange.Rows.Count r = 1 For r = rCount To 2 Step -1 Rows(r).Insert Next End Sub Regards, Per "MarshaMarsha" skrev i meddelelsen ... I need to add a blank row in between each row and the length of the document is seven pages; so, is there a way I can do this without having to add them individually?? If you can help me with this........thanks for saving my sanity!!! |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Adding new rows in between rows...
A simple macro will do this for you but the conditions in the macro will
depend on the contents of your 7 pages of data. For example, the following macro will insert a blank row between each of your existing rows and will stop as soon as it encounters a blank cell in column A (the blank cell is the signal to end the procedure or it will continue looping and creating new rows until it reaches row 65526. Sub InsertRows() Range("A2").Select 'Starting location for the procedure Do While ActiveCell < "" 'Do the actions until you reach a blank cell Selection.Insert Shift:=xlDown 'Insert a blank row ActiveCell.Offset(2, 0).Select 'Move to next row to be tested Loop 'Start again from the Do While line End Sub If you have data in every row in column A for all 7 pages then the macro will work for you. If your data is a mix of blank and non-blank rows in column A, you would have to look for a pattern that can be coded into the procedure. If you can send me a sample, I'd be happy to review it. "MarshaMarsha" wrote in message ... I need to add a blank row in between each row and the length of the document is seven pages; so, is there a way I can do this without having to add them individually?? If you can help me with this........thanks for saving my sanity!!! |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Adding new rows in between rows...
Do you need to insert a row beyween every row?
This would make for difficult sorting, filtering, copying, pasting and other functions. Perhaps, if just for appearance, you could double the height of the rows? Gord Dibben MS Excel MVP On Wed, 26 Mar 2008 11:31:00 -0700, MarshaMarsha wrote: I need to add a blank row in between each row and the length of the document is seven pages; so, is there a way I can do this without having to add them individually?? If you can help me with this........thanks for saving my sanity!!! |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Adding new rows in between rows...
Here's a method which is much faster than a macro -- practically
instantaneous: In an unused column, say G, enter 1 in G1 and 2 in G2. Select G1:G2, use the fill handle & drag to the bottom of your data (if the column is adjacent to your data, double-click the fill handle). Say this goes to row 4000. You now have #s from 1-4000. Copy them, paste them below so you'd have 1-4000 again in cells G4001:G8000. So now you have 2 sets of #s 1-4000. Select all your data and SORT by column G. Voila, your data is double-spaced -- Excel sorted the blank rows into place. Now simply clear column G!! Bob Umlas Excel MVP "Mike" wrote in message ... Sub insertrow() lastRow = Range("A" & Rows.Count).End(xlUp).Row * 2 MsgBox lastRow For i = 1 To lastRow i = i + 1 Range("A" & i).EntireRow.Insert Next End Sub "MarshaMarsha" wrote: I need to add a blank row in between each row and the length of the document is seven pages; so, is there a way I can do this without having to add them individually?? If you can help me with this........thanks for saving my sanity!!! |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Adding new rows in between rows...
Great solution, Bob. Once again, simplicity and a good practical mind
prevail over macro wizardry! ************************* "Bob Umlas" wrote in message ... Here's a method which is much faster than a macro -- practically instantaneous: In an unused column, say G, enter 1 in G1 and 2 in G2. Select G1:G2, use the fill handle & drag to the bottom of your data (if the column is adjacent to your data, double-click the fill handle). Say this goes to row 4000. You now have #s from 1-4000. Copy them, paste them below so you'd have 1-4000 again in cells G4001:G8000. So now you have 2 sets of #s 1-4000. Select all your data and SORT by column G. Voila, your data is double-spaced -- Excel sorted the blank rows into place. Now simply clear column G!! Bob Umlas Excel MVP "Mike" wrote in message ... Sub insertrow() lastRow = Range("A" & Rows.Count).End(xlUp).Row * 2 MsgBox lastRow For i = 1 To lastRow i = i + 1 Range("A" & i).EntireRow.Insert Next End Sub "MarshaMarsha" wrote: I need to add a blank row in between each row and the length of the document is seven pages; so, is there a way I can do this without having to add them individually?? If you can help me with this........thanks for saving my sanity!!! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Adding rows | Excel Discussion (Misc queries) | |||
Adding five new rows every 40 rows in a spreadsheet? | Excel Discussion (Misc queries) | |||
Adding Rows offsets to working rows across two worksheets | Setting up and Configuration of Excel | |||
Adding rows | Setting up and Configuration of Excel | |||
adding ROWS | Excel Discussion (Misc queries) |