Insert Rows - double-spacing

[Roxy]

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.

 

[Gord Dibben]

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 Insert>Module. Paste the above code in there. Save the
workbook and hit ALT + Q to return to your workbook.

Run the macro by going to Tool>Macro>Macros.

Gord Dibben Excel MVP

 

[George Nicholson]

Possible alternatives to adding blank rows (which Gord has answered):

Take the spreadsheet that already has blank rows. Data>AutoFilter, 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, Format>Row and double the default
height. You might then want to use Format>Cells>Alignment 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)

 

[Roxy]

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

 

[Dave Peterson]

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.

 

[David McRitchie]

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/insrtrow.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..