If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Insert Rows - double-spacing
If there a way to insert a blank row after every row of
data in a spreadsheet that has been previously set up? I need to compare data in the spreadsheet with another spreadsheet that was set up with a blank row after each item. The user does not want to manually insert rows (too many of them to do this). Thanks. |
#2
|
|||
|
|||
Roxy
You're pretty well stuck with a macro to do this. 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 If not familiar with VBA and macros, see David McRitchie's site for more on "getting started". http://www.mvps.org/dmcritchie/excel/getstarted.htm In the meantime.......... To create a General Module, hit ALT + F11 to open the Visual Basic Editor. Hit CRTL + R to open Project Explorer. Find your workbook/project and select it. Right-click and InsertModule. Paste the above code in there. Save the workbook and hit ALT + Q to return to your workbook. Run the macro by going to ToolMacroMacros. Gord Dibben Excel MVP On Wed, 1 Sep 2004 07:11:10 -0700, "Roxy" wrote: If there a way to insert a blank row after every row of data in a spreadsheet that has been previously set up? I need to compare data in the spreadsheet with another spreadsheet that was set up with a blank row after each item. The user does not want to manually insert rows (too many of them to do this). Thanks. |
#3
|
|||
|
|||
Possible alternatives to adding blank rows (which Gord has answered):
Take the spreadsheet that already has blank rows. DataAutoFilter, select a column and filter on "Non-Blanks". Copy the results to a new sheet: same data, no blank rows. If you are doing a visual comparison rather than strict row# to row# comparison: select the entire sheet, FormatRow and double the default height. You might then want to use FormatCellsAlignment to set the Vertical alignment to force all entries to the top or bottom of your double-height rows. You will have half the number of rows than what you are comparing to, but they should "line up" pretty much the same. Another (non-macro) method of adding blank rows: add a helper column. Use FillSeries to populate it with a series of consecutive even numbers (2,4,6,8...) as far down as you have data. Below your current data, use FillSeries to continue populating the column with consecutive odd numbers (1,3,5,7...) that go at leasat as high as the even numbers you created. Sort on that column so you get 1,2,3,4,5,6... (alternating data and blank rows) -- George Nicholson Remove 'Junk' from return address. "Roxy" wrote in message ... If there a way to insert a blank row after every row of data in a spreadsheet that has been previously set up? I need to compare data in the spreadsheet with another spreadsheet that was set up with a blank row after each item. The user does not want to manually insert rows (too many of them to do this). Thanks. |
#4
|
|||
|
|||
Thanks Gordon for your reply. It didn't work. I had a
VBA programmer at work tweak it a little. Here is what we used. Sub InsertALTrows() 'David McRitchie, misc 2001-06-30 Application.ScreenUpdating = False Application.Calculation = xlCalculationManual 'pre XL97 xlManual Dim i As Integer For i = Selection(150).Row To Selection(1).Row + 1 Step -1 Rows(i).EntireRow.Insert With Rows(i) .RowHeight = 12.75 End With Next i Application.Calculation = xlCalculationAutomatic 'pre XL97 xlAutomatic Application.ScreenUpdating = True End Sub You may need to change this part to include the maxium # of rows of data that you have. For i = Selection(150).Row -----Original Message----- Roxy You're pretty well stuck with a macro to do this. 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 If not familiar with VBA and macros, see David McRitchie's site for more on "getting started". http://www.mvps.org/dmcritchie/excel/getstarted.htm In the meantime.......... To create a General Module, hit ALT + F11 to open the Visual Basic Editor. Hit CRTL + R to open Project Explorer. Find your workbook/project and select it. Right-click and InsertModule. Paste the above code in there. Save the workbook and hit ALT + Q to return to your workbook. Run the macro by going to ToolMacroMacros. Gord Dibben Excel MVP On Wed, 1 Sep 2004 07:11:10 -0700, "Roxy" wrote: If there a way to insert a blank row after every row of data in a spreadsheet that has been previously set up? I need to compare data in the spreadsheet with another spreadsheet that was set up with a blank row after each item. The user does not want to manually insert rows (too many of them to do this). Thanks. . |
#5
|
|||
|
|||
Another option is to get rid of the blank rows in the other worksheet.
Just sort it and those blanks should go to the bottom. Roxy wrote: If there a way to insert a blank row after every row of data in a spreadsheet that has been previously set up? I need to compare data in the spreadsheet with another spreadsheet that was set up with a blank row after each item. The user does not want to manually insert rows (too many of them to do this). Thanks. -- Dave Peterson |
#6
|
|||
|
|||
There are other variations as well on the web page describing the
macro, and I certainly hope the webpage is useful in following or in customizing any of the macros.. Some Macros for inserting blank rows on alternate lines (#AltBlanks) http://www.mvps.org/dmcritchie/excel....htm#AltBlanks I'd certainly avoid changing a macro to restrict it to a particular number of rows if it is for general use. Another trick that might work for you is to select all cels (Ctrl+A) then change the height of all rows. This way you can sort without having those empty rows getting lost and interferring with the last used cell. Not that last cell isn't always going to b a problem.. -- HTH, David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001] My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm Search Page: http://www.mvps.org/dmcritchie/excel/search.htm "Roxy" wrote in message ... Thanks Gordon for your reply. It didn't work. I had a VBA programmer at work tweak it a little. Here is what we used. Sub InsertALTrows() 'David McRitchie, misc 2001-06-30 Application.ScreenUpdating = False Application.Calculation = xlCalculationManual 'pre XL97 xlManual Dim i As Integer For i = Selection(150).Row To Selection(1).Row + 1 Step -1 Rows(i).EntireRow.Insert With Rows(i) .RowHeight = 12.75 End With Next i Application.Calculation = xlCalculationAutomatic 'pre XL97 xlAutomatic Application.ScreenUpdating = True End Sub You may need to change this part to include the maxium # of rows of data that you have. For i = Selection(150).Row -----Original Message----- Roxy You're pretty well stuck with a macro to do this. 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 If not familiar with VBA and macros, see David McRitchie's site for more on "getting started". http://www.mvps.org/dmcritchie/excel/getstarted.htm In the meantime.......... To create a General Module, hit ALT + F11 to open the Visual Basic Editor. Hit CRTL + R to open Project Explorer. Find your workbook/project and select it. Right-click and InsertModule. Paste the above code in there. Save the workbook and hit ALT + Q to return to your workbook. Run the macro by going to ToolMacroMacros. Gord Dibben Excel MVP On Wed, 1 Sep 2004 07:11:10 -0700, "Roxy" wrote: If there a way to insert a blank row after every row of data in a spreadsheet that has been previously set up? I need to compare data in the spreadsheet with another spreadsheet that was set up with a blank row after each item. The user does not want to manually insert rows (too many of them to do this). Thanks. . |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
How do I add a ruler to an Excel spreedsheet? | Bob | General Discussion | 3 | September 1st, 2004 04:11 PM |
macro to insert rows and copy and paste | STEVE | Worksheet Functions | 1 | June 4th, 2004 06:26 PM |
Double Spacing in Outlook because of Formatting Style | Prairie Girl | Installation & Setup | 2 | May 27th, 2004 02:58 PM |
trying to run a sort to highlight rows and clear the cells in those rows.... | James | Worksheet Functions | 2 | December 23rd, 2003 01:38 PM |