Start adding blank rows at row 4 (vba code)

[Pierre]

Have the following code which works well and adds blank rows Would
like to add rows "starting at row 4", and not before; so it doesn't
mess up my column headings . . .where would I insert what code? Many
thanks in advance for any thoughts.

Pierre

Sub InsertRows()
Application.ScreenUpdating = False
Dim numRows As Integer
Dim r As Long
r = Cells(Rows.Count, "A").End(xlUp).Row
numRows = 8
For r = r To 1 Step -1
ActiveSheet.Rows(r + 1).Resize(numRows).Insert
Next r
Application.ScreenUpdating = True
End Sub

 

[Jim Cone]

Replace this line...
For r = r To 1 Step -1
With...
For r = r To 4 Step -1
--
Jim Cone
Portland, Oregon USA
http://tinyurl.com/ExtrasForXL




"Pierre" <[email protected]>
wrote in message
Have the following code which works well and adds blank rows Would
like to add rows "starting at row 4", and not before; so it doesn't
mess up my column headings . . .where would I insert what code? Many
thanks in advance for any thoughts.

Pierre

Sub InsertRows()
Application.ScreenUpdating = False
Dim numRows As Integer
Dim r As Long
r = Cells(Rows.Count, "A").End(xlUp).Row
numRows = 8
For r = r To 1 Step -1
ActiveSheet.Rows(r + 1).Resize(numRows).Insert
Next r
Application.ScreenUpdating = True
End Sub

 

[Dave Peterson]

I wouldn't use r as both the last row variable and the looping variable.

But I'm not sure what you're doing with that lastrow stuff.

Option Explicit
Sub InsertRows()

Dim numRows As Long 'Don't use "As Integer". It's not worth the trouble.
Dim LastRow as long
Dim r As Long

numRows = 8
LastRow = Cells(Rows.Count, "A").End(xlUp).Row

Application.ScreenUpdating = False
For r = LastRow To 4 Step -1
ActiveSheet.Rows(r + 1).Resize(numRows).Insert
Next r
Application.ScreenUpdating = True

End Sub

 

[Dave Peterson]

Ignore the statement about not sure what you're doing with the lastrow stuff.

 

[Pierre]

Ignore the statement about not sure what you're doing with the lastrow stuff.

Thank you Dave and Jim, for the replies. Worked like a charm.

Pierre