How to insert rows at constant intervals in a large worksheet quickly?

Hi there,

For Excel 2013, I have a sheet with about 10,000+ rows, each of which contains 63 columns filled with data. I'm trying to add a new row at certain intervals in the sheet. The first time I want to do this is at row 9, followed by intervals of 8, until the end of the sequence of data. I also need to add a bit of formatting to each new row produced.

I am currently using a loop for this. While this works and does what I want it to, it is agonisingly slow and crashes fairly frequently if I try to update the entire sheet. I was wondering if anyone has any idea or alternative of how to do this process quickly? (I've seen online examples using arrays that add rows quickly, but I'm fairly new to VBA and struggle to understand or reverse engineer them.)

I've included my code in case you want a clearer picture of what I'm doing. Any help is much appreciated!

Please Login or Register  to view this content.

One way

Please Login or Register  to view this content.

@Steingar
11500/7 will more than 1.5k rows changes. Use sort is fast than insert. Guru Jindon his code should the fastest.

Hi all,

I tried what you all recommended and I found the following:

@Jindon your code worked great! There were a few aspects out of wack, like the number of rows inserted and where the sequence started, but with a bit of fiddling I've got it to suit my requirements. Thank you so much for your recommendation, and the problem is now solved.

@Crooza I wasn't able to get your code to work satisfactory unfortunately, but thanks for your comment.

@jolivanes I've taken what you said about working backwards on-board when it comes to adding/deleting rows, and I understand the reason why, so thanks for the recommendation! As an aside, I set up a timer for my program and found that the difference between running the loop without formatting vs. with formatting was negligible, so it appears that the bottleneck was with adding the rows and not the formatting as was theorised.

Once again, thanks for everyone's help, and purely for interest's sake and any future googlers, the correctly altered code is as follows:

Please Login or Register  to view this content.