How to select Every Third Row in Excel

Sometimes, you may need to select or highlight the nth Row of your dataset. Doing so manually can be tedious and time-consuming. Thankfully, Excel has in-built tools and features that can be used to achieve this task. In this article, we shall discuss quick and easy ways that can be used to select the third Row in Excel.

Using VBA Codes to select every Third Row in Excel

VBA tools have proved to be powerful tools in Excel. Have you ever tried selecting every Row manually? If yes, you can concur that the process is tiresome and time-consuming. To avoid all these downfalls, you can use the VBA tool to select every nth Row. Below are steps to follow while selecting every third Row of your dataset.

1. Open the Excel application.

2. Open the worksheet that you wish to select all its 3rd Row.

3. Click on the Developer tab on the Ribbon, and then locate the Visual Basic button.

4. In the Visual Basic screen, click the Insert tab on the Ribbon and select the Module button.

5. Type the following code in the empty module.

Sub SelectEveryThirdRow()

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

6. Press the F5 button to run the code.

7. Close the Visual Basic Screen, and then select all the cells of the dataset you wish to select in the Third Row.

8. Click on the Developer tab on the Ribbon, and then locate the Macros button. Select the save Macros and click the Run button. That is all you need to do.

VBA code to select every third Row of a Table

Here are the codes:

Sub EveryThirdRow()

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

Using the OFFSET Function

OFFSET function can be used to select every nth Row in Excel. The general formula that is used to do so is =OFFSET($A$1,(ROW()-1)*n,0 ). Similarly, the OFFSET function can be used together with the ROW function to return the values in the 3rd Row. Here are the steps to follow:

1 Open the Excel application.

2. Open the worksheet that you wish to select all its 3rd Row.

3. Click on the empty cell in the next empty column. Double-click on the cell to activate the editing tool.

4. Locate the Formula bar, and type the Equal sign followed by the OFFSET function. That is, =OFFSET(

5. Add the cell index of the column that contains your data. Then, check for the 3rd Row in your dataset using the ROW function. That is =OFFSET($A$1,(ROW()-1)*3,0 )

6 Hit the Enter button.

7. Drag and copy the formulae to other cells.