# Do Something Every so Many Rows with a Macro in Excel

Add to Favorites
Author:

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.

## Question? Ask it in our Excel Forum

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: (New Easier Version for Excel 2010 and Later) How to increment a value, number, percent, e...
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 met...
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...
Guide to Creating Charts with a Macro in Excel
Tutorial: How to add, edit, and position charts in Excel using VBA. This tutorial covers what to do ...
Tutorial Details
Downloadable Files: Excel File
Similar Content
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: (New Easier Version for Excel 2010 and Later) How to increment a value, number, percent, e...