Start adding blank rows at row 4 (vba code)

  • Thread starter Thread starter Pierre
  • Start date Start date
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
 
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
 
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
 
Back
Top