How to select every other row or every nth row in Excel

Select every other or every nth row using Ctrl key

If you are working with a small dataset, manually selecting every other or every nth row can be done using the Ctrl key. Here's how:

1. Click on the first row you want to select, and then press and hold the Ctrl key on your keyboard.
2. While holding the Ctrl key, click on every other row or on every nth row.
3. Release the Ctrl key, and you will have selected the desired rows.

This method works well for small tables, where the number of rows is manageable. However, for larger datasets, this manual selection can be time-consuming and prone to errors. In such cases, it is more efficient to use other methods discussed below.

Selecting every other row with formula and filter

When working with larger worksheets, the task of selecting alternate rows becomes much simpler by utilizing a helper column and the Filter functionality. Follow the steps below to achieve this:

1. In an empty column next to your dataset, enter one of these formulas:

   =ISODD(ROW())

   =ISEVEN(ROW())

   These formulas utilize the ROW function to retrieve the number of each row, while the ISODD and ISEVEN functions determine if the row number is odd or even, respectively. This will help you identify the rows you want to select.

2. Add filters to the column headers. For this, select any cell within your dataset and click the Filter button on the Data tab, or use the Ctrl + Shift + L shortcut.
3. In the helper column, click on the filter arrow and uncheck either TRUE or FALSE depending on whether you want to select odd or even rows.
4. Click OK, and Excel will filter the data, displaying only the rows you have selected.
5. Select the filtered rows using your mouse or the Shift key. Refer to the Select multiple cells in Excel guide for more detailed instructions.

When selecting filtered data, it's important to note that Excel only selects the cells that are visible. Therefore, if you highlight the visible cells, only the alternate rows will be highlighted once the filter is removed. Similarly, if you copy the visible rows to another location, only every other row will be copied.

Selecting every nth row with formula and filter

To select every nth row in Excel, you can modify the previous method slightly, by using the MOD formula for the helper column:

Where:

• m is the row number of the first cell in your dataset minus 1
• n is the Nth row you want to select

For instance, if your data begins in row 3 and you want to select every 4^th row, then m would be 2 (row 3 minus 1) and n would be 4:

=MOD(ROW() - 2, 4)

With the formula established, follow these steps to filter every Nth row in Excel:

1. Determine the m and n values for the MOD formula and enter it in the Helper column.
2. Add Excel's auto filters to your set of data.
3. In the helper column, click on the filter arrow and uncheck the box next to Select All.
4. Check the box next to "0" to show only "0" values in the helper column. These values correspond to every Nth row, according to your chosen value of n. Click OK to apply the filter.
5. Select all of the visible rows that appear after filtering.
6. Remove the filter and delete the helper column if it's no longer needed.

Excel VBA to select every other row

If you find yourself frequently performing the task of selecting every other row in Excel, you can automate the process using VBA. Below, you'll find two versions of the code: one to select every odd row and another to select every even row.

Select every even row in Excel

Similar to the code for odd rows, this code iterates through each row in the range, adding every even row to a new range, and then selects the new range if it's not empty.

Sub SelectEvenRows() Dim selectedRange As Range Dim i As Integer Dim newRange As Range ' define selected range Set selectedRange = Selection ' for each row in the selected range For i = 2 To selectedRange.Rows.Count Step 2 ' add every even row to a new range If newRange Is Nothing Then Set newRange = selectedRange.Rows(i) Else Set newRange = Union(newRange, selectedRange.Rows(i)) End If Next i ' select new Range If Not newRange Is Nothing Then newRange.Select End If End Sub

To add the codes to your workbook, follow the steps described in How to insert VBA code in Excel.

To select every other row in Excel using VBA, follow these steps:

1. Select the target range.
2. Press Alt + F8 to open the macro dialog box.
3. Choose the desired macro - SelectOddRows or SelectEvenRows.
4. Click Run to execute the code.

By utilizing these VBA codes, you can automate the process of selecting alternate rows, saving you time and effort in your data manipulation tasks.

Excel VBA to select every Nth row

To select every Nth row in Excel using VBA, you can create a UserForm, which serves as a graphical interface for the user to input the N parameter, and call that user form from the macro.

First, design the UserForm with the necessary elements and properties as shown in the screenshot below:

After completing the form's design, add the code for the OK button that will run the macro. For this, double-click the button on the user form, and then paste the code into the Code window. Alternatively, you can right-click on the form in the Project Explorer and select View Code.

In this code, we loop through each row in the range with the specified step size (N), adding each Nth row to a new range. Finally, we select the new range and hide the UserForm.

Next, create the SelectEveryNRow macro by adding the following code to your workbook's module:

When you run the macro, a small pop-up dialog box will appear, prompting you to specify the value for N. Enter the desired number, and the macro will select every Nth row in the target range.

Practice workbook for download

VBA to select every other or every nth row in Excel (.xlsm file)