Inserting multiple non-consecutive rows

P

piper1963

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!
 
N

Norman Jones

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?
Click to expand...

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
 
M

Merna

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?"
 
G

Gord Dibben

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 Insert>Module. 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 Tool>Macro>Macros.

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


Gord Dibben MS Excel MVP

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?"
Click to expand...

Gord Dibben MS Excel MVP
 
Top