How to Delete every other row or every Nth row in Excel

In your Excel life, many situations occur when you want to delete alternate rows or every other row in your worksheets. For example, you have been asked to share the data of every alternate month (rows carrying data for Jan, Mar, May, Jul, Sep, and Nov) and want to shift the remaining data to another worksheet.

The easy way to get this done is to select every alternate row in your worksheet and delete them with a single click. Though this method generally works, in this tutorial, we will also cover other techniques that will quickly select and delete every alternate or every nth row.

Topics Covered:

1. Delete every other row in Excel using filtering method
2. Steps to delete alternate rows in Excel using VBA
3. Delete every Nth row in Excel

Delete every other row in Excel using filtering method

The quick and easy method to delete every other row is to filter the alternate rows, select them and delete them together. Perform the following steps to delete every other row in Excel using the filtering method:

1. Open the worksheet, select an empty column next to your original data. Add a helper column where we will enter a sequence of zeros and ones (it aids to filter out the odd or even row). Just enter 0 in the first cell, 1 in the second and quickly drag it to the last cell, it will enter the rest of the counting for you.
   OR
   You can also opt for the below-given formula.

The explanation of above formula is very easy. The Excel ROW function yields the current row number, the MOD function divides the row number by 2 and returns the remainder rounded to the integer.

1. To filter the even, click on Data tab > click on Data -> Filter option.
   
2. You will notice that the drop-down filter arrows will occur at the top of the header cells. All you need to do is to click on the arrow button in the Helper column, you will have the following options:
   • 0 to delete even rows
   • 1 to delete odd rows

Select '0 to delete every row' option.

1. Since in our case, we are asked to remove even values, therefore we will select the rows with "0" values, so we filter them:
   
2. As a result, all the rows carrying 1 will be hidden. Therefore, we will select the visible 0 rows and click on Delete Row option:
   
3. Following the above step has left you with an empty table, but don't worry, the "1" rows are still there.
   
4. To make the remaining data visible again, we will remove the filter option by clicking the Filter button.
   

Note: Once you unfiltered the rows, you may get the sequence of 0 and 1 again, as the formula applied in column C recalculates the mod for the remaining rows, but our job is done, so we don't require it anymore.

1. Delete the helper columns as it's no longer of nay use.
2. You will have your filtered data after deleting every alternative row.
   

Simple, isn't it? Let's move to the next technique, where we will delete every alternative row using VBA coding.

How to delete alternate rows in Excel with VBA

If you are someone interested in coding and love to work on advanced Excel, you can also work on Excel VBA and create your form using macro. It also benefits when you create a table for someone else; your users may not know where to look for the Form button. Moreover, many people still don't know that something like that exists in Microsoft Excel. To put more focus on this feature, you can directly code it on VBA macro, and put a special button directly on the Excel worksheet to run that macro.

Follow the below steps.

1. Go to Developer tab->click on the Visual Basic Window.
   
2. It will open the editor window, click on Insert-> Module.
3. To open the Data entry form in your Excel window, enter the below code.

CODE:

Once you are done, writing the program, the next step is to run the VBA code. Let's have a look how to run the created macro in Excel worksheet.

How to delete every other row in Excel using the macro

Following are steps to insert the macro in your Excel worksheet with the help of VBA editor:

1. Go to Developer tab->click on the Visual Basic Window.
   
2. It will open the editor window.
3. Click on the run tab or directly press the F5 shortcut key to run the macro.
   
4. A dialog window will appear asking the range, quickly enter the required range from your table for which you want to delete the alternative row and prompt you to select a range. Click on the OK button.
   
5. As a result, every other row will be deleted from the selected range of your Excel table.

Quick and easy, right!

so far in this tutorial we covered different methods to delete every other row, what if we want to delete Nth row in Excel worksheet.

Steps to delete every Nth row in Excel

Deleting every Nth row, is an updated version of the filtering technique to delete every other row. With a little twist in the above mod formula, we can expand the filtering technique to delete every nth row. The formula is shown below:

Where:

• m represents the row number of the first cell with data - 1
• n represents the number of the Nth row that you would like to delete from your worksheet

Example 1: Let's say you have the following data (starting from row 2) and you are asked to delete every 4^rd row.

We will use the above formula, wherein n will be equal to 4 and m will be equal to 1.

Therefore, the formula becomes:

Following are steps to insert the macro in your Excel worksheet with the help of VBA editor:

1. Open the worksheet, select an empty column next to your original data. Add a helper column where we will apply the formula to have a sequence of remainders (it helps to filter out every 4^th row).
   
2. Apply the following formula and drag it down the cells.

The above formula will divide the row number by four and return the remainder in the selected cell after every division. For example, in our case, it returns zero after dividing every fourth row because every 4th row divides by four without remainder (4,8,12, etc.).

1. Select the helper column. Go to Data-> Filter.
   
2. You will notice that the drop-down filter arrows will occur at the top of the header cells. All you need to do is to click on the arrow button in the Helper column, and uncheck all other options and only select '0 to delete every 4^th row' option.
   
3. It will filter out the cells and will have visible the rows carrying 0 as their input data. Select the visible cells, right-click and choose Delete Row from pop-up window.
   
4. The above step has left you with an empty table. To make the remaining data visible, remove the filter from the helper column and finally delete it.
   
5. You will have the below-given output, where you have deleted every 4^th row of your worksheet.
   

Following the similar technique, you can delete every 6^th, 5=7^th or any other Nth row in Microsoft Excel.