Do Something Every so Many Rows with a Macro in Excel

How to have a macro do something on a set interval of rows; for instance, input a value every 5 rows.

Sections:

The Code

Change the Code to Work for You

Notes

The Code

The macro in the workbook attached to this tutorial has a little more code and that is commented so you can understand it.

Change the Code to Work for You

You only need to change the interval, first_row, and last_row numbers.

interval is currently set to 2 and that means that the macro runs every two rows. Set this to whatever interval you want.

first_row is the first row in the worksheet that the macro should run on. Change it to fit your data set.

last_row is the last row that this should run on.

Your code goes here! is the section where you put the code that you want to run every so many rows. In my example I put: Cells(cur_row, 1).Value = "something" which will put the text "something" into Column A every 2 rows (since 2 is the interval).

You can put whatever you want here. I left this code intentionally bare so that you can use it as a template and build upon it.

Automatically find the Last Row

In the file attached to this workbook I included this piece of code:

I commented it out so it won't run by default.

This line of code: last_row = Cells(Rows.Count, 1).End(xlUp).Row is the only line of actual code and the rest is a comment. This line sets the last_row variable equal to the last row of data that you have in the worksheet. This allows you to run the macro when you already have a data set in the worksheet and don't want to hard-code the value for the last_row.

To use this, comment-out the line: last_row = 10 and uncomment the above line and replace the 1 with the number of the column that contains the list of data.

If this is confusing, just download the attached file for this tutorial and read the comments and play around with the code; it shouldn't take long to get either setup to work.

Notes

This tutorial gives you a template that you can use to run a macro on a cell at a specified interval of rows.

This macro, in its current form, only inputs some text every two rows. You will need to adjust the macro to work for your project, which shouldn't be difficult to do given the above instructions.

Make sure to download the attached file so you can work with this macro in Excel.