inserting a row(empty) afther each 4 rows of my data

[KingTamo]

Mr. Rick
your code is great but it will not work after while ..
Try to put any numeric values in range("A1:A102") for example .. and test your code
you'll find that the last empty row is row 100 only !!

[Rick Rothstein]

Mr. Rick
your code is great but it will not work after while ..
Try to put any numeric values in range("A1:A102") for example .. and test your code
you'll find that the last empty row is row 100 only !!

Good catch! Thanks for catching that... I had forgotten to account for the fact that the last row changes as the rows are inserted which is easily accounted for...

Sub Test()
  Dim R As Long
  For R = 5 To 5 * Cells(Rows.Count, "A").End(xlUp).Row Step 5
    Rows(R).Insert
  Next
End Sub

If the OP does not want to watch the screen jumping around, screen updating can be turned off, although then there is no visual feedback as to the macro's progress which may be important if there are a lot of rows to process (I have a fairly fast computer and the wait was quite noticeable for 20,000 rows).

Sub Test()
  Dim R As Long
  Application.ScreenUpdating = False
  For R = 5 To 5 * Cells(Rows.Count, "A").End(xlUp).Row Step 5
    Rows(R).Insert
  Next
  Application.ScreenUpdating = True
End Sub