How to have a macro do something on a set interval of rows; for instance, input a value every 5 rows.
Change the Code to Work for You
Sub row_interval()
'row interval
interval = 2
'first row to start on
first_row = 1
'last row to go to - above version of this goes to the last row of the data in the prescribed column
last_row = 10
For cur_row = first_row To last_row
If (cur_row - (first_row - 1)) Mod interval = 0 Then
'Your code goes here!
Cells(cur_row, 1).Value = "something"
End If
Next
End Sub
The macro in the workbook attached to this tutorial has a little more code and that is commented so you can understand it.
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.
In the file attached to this workbook I included this piece of code:
'last row to go to
'uncomment the next line to make the macro run to the end of the data in the column
'change the 1 in the next line to the number of the desired column
'last_row = Cells(Rows.Count, 1).End(xlUp).Row
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.
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.