Do Something Every so Many Rows with a Macro in Excel

Add to Favorites

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

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.

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:

'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.

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.


Downloadable Files: Excel File

Similar Content on TeachExcel
Get Data from the Worksheet into a Macro in Excel
Tutorial: Here, you'll learn how to get information from the Excel worksheet into a macro so you can...
Put Data into a Worksheet using a Macro in Excel
Tutorial: How to input data into cells in a worksheet from a macro. Once you have data in your macro...
Increment a Value Every X Number of Rows in Excel
Tutorial: How to increment a value, number, percent, etc. every so many rows in Excel. This tutorial...
Find the Next Blank Row with VBA Macros in Excel
Tutorial: Learn how to find the next empty cell in a range in Excel using VBA and Macros.  This meth...
What is a Macro in Excel?
Tutorial: This is the first step to learning about Macros for Excel and how to use them. What is a M...
Delete All Empty Rows or Blank Cells from a Range in Excel
Tutorial: How to quickly delete all empty cells or rows from a range in Excel.  This allows you to q...