How to loop a recorded macro in a specified number of times?

What is the purpose of the macro? Where does it start?

There is a pattern consists of 10 rows. (From the row 1 to the row 10.)

I want this pattern repeats 50 times. So It repeats to the row 500.

I selected the second row (the row 2) then started recording the macro.

The point is I create blank rows in these every patterns in a specified way.

Attach a sample workbook (not a picture or pasted copy). Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

OK, try this ... suggest you test it on a copy of the worksheet first:

Please Login or Register  to view this content.

Comment out a couple of lines ...

Please Login or Register  to view this content.

You're welcome.

Thanks for the rep.

OK, well, you start in row 1 and you have blocks of 10 rows including a header, eight (8) data records, and a blank row. You want to insert 5 blank rows in each block.

Your original macro started in row 2, after the first header block. After you have inserted 5 blank rows into the first block, the header for the second block will be in row 16 and you need to process from row 17. That is, having inserted the blank rows, we need to move on 15 rows to get the next block.

So, the way the loop works is this:

Please Login or Register  to view this content.

i = 1: i - 1 = 0; (i - 1) * 15 = 0; (i - 1) * 15 + 2 = 2; so the address becomes Range("A2")
i = 2: i - 1 = 1; (i - 1) * 15 = 15; (i - 1) * 15 + 2 = 17; so the address becomes Range("A17")
i = 3: i - 1 = 2; (i - 1) * 15 = 30; (i - 1) * 15 + 2 = 32; so the address becomes Range("A32")
i = 4: i - 1 = 3; (i - 1) * 15 = 45; (i - 1) * 15 + 2 = 47; so the address becomes Range("A47")
i = 4: i - 1 = 4; (i - 1) * 15 = 60; (i - 1) * 15 + 2 = 62; so the address becomes Range("A62")
and so on.

The addresses to be inserted are all offset from the first row of each block (after the previous blocks have been adjusted by inserting the blank rows.

Hope that helps. You could step through the code using F8 and watch the rows being inserted into each block if that helps you to visualise the process.

You're welcome. Thanks again for the rep.


This is an alternative approach. It starts from the bottom up so you don't need to calculate the starting point for each block ... it's always 10 rows before the block being processed. Maybe easier to follow but not as aesthetically pleasing.

Please Login or Register  to view this content.