How to Select Every Third Row in Excel (or select every Nth Row)

A few days ago, one of my VBA course students asked me if there was a way to select every third row in a dataset in Excel.

While there is no way to do this using inbuilt functionalities in Excel, it can easily be done using VBA.

Something as shown below:

In this tutorial, I will give you the VBA code and show you the exact steps to get this done in Excel.

Click here to download the example file and follow along.

Let me first give you the VBA code that will select every third row in the dataset that you have selected.

Note that I have taken every third row as an example and you can modify the code to select every second, fourth, fifth, or Nth row (or even columns) in the dataset.

VBA Code to Select Every Third Row in a Dataset in Excel

Below is the code that will select every third row in the dataset that you have selected.

Sub SelectEveryThirdRow()
'Created by Sumit Bansal at https://trumpexcel.com/
Dim MyRange As Range
Dim RowSelect As Range
Dim i As Integer
Set MyRange = Selection
Set RowSelect = MyRange.Rows(3)
For i = 3 To MyRange.Rows.Count Step 3
Set RowSelect = Union(RowSelect, MyRange.Rows(i))
Next i
Application.Goto RowSelect
End Sub

Note that this code will work only when you select a dataset. As soon as you run this code, it will select every third row in the selected dataset.

What does the Code do?

The code uses two object variables – MyRange and RowSelect.

• The selected range is assigned to the ‘MyRange’ variable. This means that as soon as you select a range of cells and run this code, the first thing it does is assigns that selected range of cells to this variable ‘MyRange’. The following line of code does this:

Set MyRange = Selection

• The second step is to assign the third row in the dataset to another object variable ‘RowSelect’. The following line in the code does this:

Set RowSelect = MyRange.Rows(3)

• When the above two steps are done, the code runs a For Next loop. It starts from the third row and runs till the variable ‘i’ reaches the number equal to the total number of rows in the dataset. The total number of rows is given by MyRange.Rows.Count. Also, note that I have used Step 3 here which means that the value of the variable ‘i’ in the loop will change in steps of 3 (for example 3, then 6, then 9, and so on). The following line of code does all this:

For i = 3 To MyRange.Rows.Count Step 3

• Within the loop, we just combine every third row in the data set using the UNION method in VBA. All these rows (i.e., every third row in the data set) is assigned to the object variable ‘RowSelect’. The following line of code does this:

Set RowSelect = Union(RowSelect, MyRange.Rows(i))

• Finally, when the loop is over, we ask the Excel application to go and select all the rows stored in the object ‘RowSelect’. The following line of code does this:

Application.Goto RowSelect

Now let’s see where you need to copy and paste this code into Excel.

Download the Example File

Where to Copy and Paste this VBA Code

Here are the steps you need to follow to copy this code in the VB Editor from where it can be run:

• Copy the code mentioned above.
• Go to the Developer tab (click here if you don’t see the developer tab in the ribbon).
• Click on Visual Basic (or use the keyboard shortcut – ALT + F11).
• In the VB Editor, right-click on any of the workbook objects (if you can’t see the Project Explorer, use the keyboard shortcut – Control + R).
• Go to the ‘Insert’ option and click on ‘Module’.
• Double-click on the Module object that is inserted.
• Paste the code in the Module code window.
• Close the VB Editor.

Once you have copied the code in the VB Editor, you can now use it in the workbook.

Click here to learn different ways to run a macro in Excel.

Note that since the workbook would have a macro code in it, you need to save it in the .xlsm or .xls extension.

Variations in the Code (Examples)

The above code would select every third row in the selected dataset.

This could be useful if you want to delete every third row or copy these and paste it into a new worksheet.

But what if you want to select every second row, or every fourth row, or every second column (or any Nth row/column for that matter).

In those cases, you can easily modify the VBA code.

Here are some example codes.

Example 1 – Select Every Fourth Row in a Dataset

Below is the code that will select every fourth row in the selected dataset:

Sub SelectEveryFourthRow()
'Created by Sumit Bansal at https://trumpexcel.com/
Dim MyRange As Range
Dim RowSelect As Range
Dim i As Integer
Set MyRange = Selection
Set RowSelect = MyRange.Rows(4)
For i = 4 To MyRange.Rows.Count Step 4
Set RowSelect = Union(RowSelect, MyRange.Rows(i))
Next i
Application.Goto RowSelect
End Sub

Note that the only change I have made here is the number in the code (from 3 to 4).

Example 2 – Select Every Second Column in a Dataset

Below is the code that will select every second column in the selected dataset:

Sub SelectEverySecondColumn()
'Created by Sumit Bansal at https://trumpexcel.com/
Dim MyRange As Range
Dim ColumnSelect As Range
Dim i As Integer
Set MyRange = Selection
Set ColumnSelect = MyRange.Columns(2)
For i = 2 To MyRange.Columns.Count Step 2
Set ColumnSelect = Union(ColumnSelect, MyRange.Columns(i))
Next i
Application.Goto ColumnSelect
End Sub

Example 3 – Selecting Every Third Row in an Excel Table

If you work with data regularly, you may be using Excel tables (you must if you aren’t already).

When using an Excel Table, you can modify the code so that you don’t have to select the data set. The code will automatically go to the Excel Table and select every third row in it ( or whatever number of row/column you have specified in the code).

So if I created an Excel Table (named Table1), I can use the below code to select every third row in it.

Sub SelectEveryThirdRow()
'Created by Sumit Bansal at https://trumpexcel.com/
Dim MyRange As Range
Dim RowSelect As Range
Dim i As Integer
Set MyRange = Range("Table1")
Set RowSelect = MyRange.Rows(3)
For i = 3 To MyRange.Rows.Count Step 3
Set RowSelect = Union(RowSelect, MyRange.Rows(i))
Next i
Application.Goto RowSelect
End Sub

The only change I have made here is that instead of using

Set MyRange = Selection

I have used

Set MyRange = Range("Table1")

How to Add this Macro to the Quick Access Toolbar

If selecting every third row is a task that you need to do often, it’s a good idea to save this macro in your personal macro workbook. Here is a detailed guide on how to save a macro in the personal macro workbook.

Once you have saved the macro in the personal macro workbook, you can add it to the Quick Access Toolbar (QAT). This way, you will have access to this macro in all the workbooks and you will be able to run it right from the QAT.

Here are the steps to save a macro to the QAT:

• Click on the Customize Quick Access Toolbar icon.
• Select ‘More Commands’.
• In the Excel Options dialogue box, in the ‘Choose command from’ dialog box, select ‘Macros’.
• Click on the Macro that you want to add to the QAT.
• Click on Add.
• Click OK.

This will add this macro in the QAT, as shown below.

Now you can simply select the data set (in which you want to select every third/Nth row), and click on the macro icon in the QAT.

Download the Example File

You May Also Like the Following Excel Tutorials:

• 3 Quick Ways to Select Visible Cells in Excel
• 24 Useful Excel Macros for VBA Beginners.
• How to Create and Use an Excel Add-in.
• How to Quickly Select Blank Cells in Excel.
• Number Rows in Excel.
• How to Select 500 cells/rows in Excel (with a single click).
• Select Till End of Data in a Column in Excel (Shortcuts)