Inserting multiple non-consecutive rows
 

I have spreadsheet populated with 200 names. I need to insert 2 blank rows
between each name. I know how to manually insert blank rows. But, is there
a quicker, more efficient way to do this? Thank you!

Inserting multiple non-consecutive rows
 

Hi Piper,

I have spreadsheet populated with 200 names. I need to insert 2
blank rows between each name. I know how to manually insert
blank rows. But, is there a quicker, more efficient way to do this?

Try:
'=============
Public Sub Tester()
Dim WB As Workbook
Dim SH As Worksheet
Dim Rng As Range
Dim LRow As Long
Dim i As Long
Dim CalcMode As Long

Set WB = Workbooks("YourBook.xls") '<<===== CHANGE
Set SH = WB.Sheets("Sheet1") '<<===== CHANGE

On Error GoTo XIT
With Application
CalcMode = .Calculation
.Calculation = xlCalculationManual
.ScreenUpdating = False
End With

LRow = Cells(Rows.Count, "A").End(xlUp).Row

For i = LRow To 1 Step -1
Rows(i + 1).Resize(2).Insert
Next i

XIT:
With Application
.Calculation = CalcMode
.ScreenUpdating = True
End With
End Sub
'<<=============

If you are not familiar with macros, you may wish to visit David McRitchie's
'Getting Started With Macros And User Defined Functions' at:

http://www.mvps.org/dmcritchie/excel/getstarted.htm


---
Regards,
Norman


"piper1963" wrote in message
...
I have spreadsheet populated with 200 names. I need to insert 2 blank rows
between each name. I know how to manually insert blank rows. But, is
there
a quicker, more efficient way to do this? Thank you!

Inserting multiple non-consecutive rows
 

Norma,

I have a similar problem to Piper's. But this solution you gave is Greek to
me, as is the information in McRitchie's page for macros. Is there a simpler
way? Or can you translate this into "English?"

"Norman Jones" wrote:

Hi Piper,

I have spreadsheet populated with 200 names. I need to insert 2
blank rows between each name. I know how to manually insert
blank rows. But, is there a quicker, more efficient way to do this?

Try:
'=============
Public Sub Tester()
Dim WB As Workbook
Dim SH As Worksheet
Dim Rng As Range
Dim LRow As Long
Dim i As Long
Dim CalcMode As Long

Set WB = Workbooks("YourBook.xls") '<<===== CHANGE
Set SH = WB.Sheets("Sheet1") '<<===== CHANGE

On Error GoTo XIT
With Application
CalcMode = .Calculation
.Calculation = xlCalculationManual
.ScreenUpdating = False
End With

LRow = Cells(Rows.Count, "A").End(xlUp).Row

For i = LRow To 1 Step -1
Rows(i + 1).Resize(2).Insert
Next i

XIT:
With Application
.Calculation = CalcMode
.ScreenUpdating = True
End With
End Sub
'<<=============

If you are not familiar with macros, you may wish to visit David McRitchie's
'Getting Started With Macros And User Defined Functions' at:

http://www.mvps.org/dmcritchie/excel/getstarted.htm


---
Regards,
Norman


"piper1963" wrote in message
...
I have spreadsheet populated with 200 names. I need to insert 2 blank rows
between each name. I know how to manually insert blank rows. But, is
there
a quicker, more efficient way to do this? Thank you!

Inserting multiple non-consecutive rows
 

Maybe this would be a little easier.

Sub InsertRows()
Application.ScreenUpdating = False
Dim numRows As Integer
Dim r As Long
numRows = 2
For r = 2000 To 1 Step -1
ActiveSheet.Rows(r + 1).Resize(numRows).EntireRow.Insert
Next r
Application.ScreenUpdating = True
End Sub

First...create a backup copy of your original workbook.

Now...with your workbook open

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 by name and select it.

Right-click and InsertModule. Paste the code in there. Save the
workbook and hit ALT + Q to return to your workbook and worksheet you want
altered.

Run the macro by going to ToolMacroMacros.

You can also assign this macro to a button or a shortcut key combo


Gord Dibben MS Excel MVP

On Wed, 21 Jun 2006 15:01:02 -0700, Merna
wrote:

Norma,

I have a similar problem to Piper's. But this solution you gave is Greek to
me, as is the information in McRitchie's page for macros. Is there a simpler
way? Or can you translate this into "English?"

"Norman Jones" wrote:

Hi Piper,

I have spreadsheet populated with 200 names. I need to insert 2
blank rows between each name. I know how to manually insert
blank rows. But, is there a quicker, more efficient way to do this?

Try:
'=============
Public Sub Tester()
Dim WB As Workbook
Dim SH As Worksheet
Dim Rng As Range
Dim LRow As Long
Dim i As Long
Dim CalcMode As Long

Set WB = Workbooks("YourBook.xls") '<<===== CHANGE
Set SH = WB.Sheets("Sheet1") '<<===== CHANGE

On Error GoTo XIT
With Application
CalcMode = .Calculation
.Calculation = xlCalculationManual
.ScreenUpdating = False
End With

LRow = Cells(Rows.Count, "A").End(xlUp).Row

For i = LRow To 1 Step -1
Rows(i + 1).Resize(2).Insert
Next i

XIT:
With Application
.Calculation = CalcMode
.ScreenUpdating = True
End With
End Sub
'<<=============

If you are not familiar with macros, you may wish to visit David McRitchie's
'Getting Started With Macros And User Defined Functions' at:

http://www.mvps.org/dmcritchie/excel/getstarted.htm


---
Regards,
Norman


"piper1963" wrote in message
...
I have spreadsheet populated with 200 names. I need to insert 2 blank rows
between each name. I know how to manually insert blank rows. But, is
there
a quicker, more efficient way to do this? Thank you!




Gord Dibben MS Excel MVP