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

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:

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:

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:

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:

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: 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:

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. The only change I have made here is that instead of using I have used

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

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)

For i = 3 To MyRange.Rows.Count Step 3 This is on office 365. what did I do wrong ? MOD(ROW(), n) where n is the n’th row you wish to identify or filter. For example, MOD(ROW(), 3) will have a value of 1, 2, and 0 every third row. You can filter columns and select or un-select as needed. please support. and give the best guidance. thanks Hemant Sharma 9911195566 I am non VBA guy whereas i use standard Excel logics to do such activities…. the way which i know to extract the nth row without VBA is ={IF(ROWS($K$1:K1)>COUNTIF($I$1:$I$14,0),””,INDEX(A$1:A$14,SMALL(IF(MOD((ROW($B$1:$B$14)-ROWS($B$1)+1),3)=0,(ROW($B$1:$B$14)-ROWS($B$1)+1)),ROWS($K$1:K1))))} The above formula has to be used with CSE. Hope this will also meet the requirement……. While a lot can be done with formulas, it can not select cells/rows. The intent here is not to extract every third row, but to select it. For example, suppose I have dataset and I want to delete every third row (or change the value in every third cell in a column), then I can use VBA to get this done faster. However this case I suppose it would be faster to use the functions within Excel? Input for example the numbers 1, 2, 3 in a new column – drag the column all the way down to the end (by marking the fist tre rows of that column). In a new sheet use the if-formula to import rows where the new column equals to 3. Mark everything, import as text, delete doubles (and delete the remaining blank row (assuming there are now doubles to keep))… Just an example of how I would solve the problem without using VBA (since I’m terribly bad at it at the moment)… Keep up the good work! OC I don’t need the extra column – typical of me to make the solution more complicated than neccessary… Yes I clearly see the advantage of VBA-solutions. Especially it the Excel-calculations are going to be made by someone else than me. I do from time to time create sheets to be used by others, and in those cases I also have to put in time for explanation/creating a manual… Thanks a lot for a great site! 😀 And i need your help about my project, i need print all with vlookup,, like a mailmerge in word,, maybe you cant show me,, how print all data using vlook up,, sorry for my bad english.. and before that,, thank you so much,, sumit,, you tutorial is great Sub SelectEveryThirdRow() Dim MyRange As Range Dim RowSelect As Range Dim i As Integer Set MyRange = Selection Set RowSelect = MyRange.Rows(N) For i = N To MyRange.Rows.Count Step N Set RowSelect = Union(RowSelect, MyRange.Rows(i)) Next i Application.Goto RowSelect End Sub In the above code, you can change N with the number you want.