This short tutorial explains how to delete every other row in Excel by filtering or with VBA code. You will also learn how to remove every 3rd, 4th or any other Nth row.
There are many situations when you may need to delete alternate rows in Excel worksheets. For example, you might want to keep data for even weeks (rows 2, 4, 6, 8, etc.) and move all odd weeks (rows 3, 5, 7 etc.) to another sheet.
Generally, deleting every other row in Excel boils down to selecting alternate rows. Once the rows are selected, a single stroke on the Delete button is all it takes. Further on in this article, you will learn a few techniques to quickly select and delete every other or every Nth row in Excel.
In essence, a common way to erase every other row in Excel is this: first, you filter alternate rows, then select them, and delete all at once. The detailed steps follow below:
Alternatively, you can use this formula:
The formula's logic is very simple: the ROW function returns the current row number, the MOD function divides it by 2 and returns the remainder rounded to the integer.
As the result, you have 0 in all even rows (because they are divided by 2 evenly without remainder) and 1 in all odd rows:
To have it done, select any cell in your Helper column, go to the Data tab > Sort and Filter group, and click the Filter button. The drop-down filter arrows will appear in all header cells. You click the arrow button in the Helper column and check one of the boxes:
In this example, we are going to remove the rows with "0" values, so we filter them:
As the result, only the even weeks are left in our worksheet, the odd weeks are gone!
Tip. If you'd like to move every other row to somewhere else rather than delete them altogether, first copy the filtered rows and paste them to a new location, and then delete the filtered rows.
If you are not willing to waste your time on a trivial task such as deleting every other row in your Excel worksheets, the following VBA macro can automate the process for you:
Insert the macro in your worksheet in the usual way via the Visual Basic Editor:
Done! Every other row in the selected range is deleted:
For this task, we are going to expand the filtering technique that we've used to remove every other row. The difference is in the formula on which the filtering is based:
Let's say your data begins in row 2 and you want to delete every 3rd row. So, in your formula n equals 3, and m equals 1 (row 2 minus 1):
=MOD(ROW() - 1, 3)
If our data began in row 3, then m would equal 2 (row 3 minus 1), and so on. This correction is needed to sequentially number the rows, starting with the number 1.
What the formula does is divide a relative row number by 3 and return the remainder after division. In our case, it yields zero for every third row because every third number divides by 3 without remainder (3,6,9, etc.):
And now, you perform the already familiar steps to filter "0" rows:
In a similar fashion, you can delete every 4th, 5th or any other Nth row in Excel.
Tip. In cased you need to remove rows with irrelevant data, the following tutorial will come in helpful: How to delete rows based on cell value.
I thank you for reading and hope to see you on our blog again next week.
Table of contents