Start adding blank rows at row 4 (vba code)

P

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
 
J

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
 
D

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
 
D

Dave Peterson

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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top