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

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.

How to delete every other row in Excel by filtering

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:

  1. In an empty column next to your original data, enter a sequence of zeros and ones. You can quickly do this by typing 0 in the first cell and 1 in the second cell, then copying the first two cells and pasting them down the column until the last cell with data.

    Alternatively, you can use this formula:

    =MOD(ROW(),2)

    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:
    Identify every other row using a Mod formula.

  2. Depending on whether you want to delete even or odd rows, filter out ones or zeros.

    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:

    • 0 to delete even rows
    • 1 to delete odd rows

    In this example, we are going to remove the rows with "0" values, so we filter them:
    Filter the rows with 0 values.

  3. Now that all "1" rows are hidden, select all the visible "0" rows, right-click the selection and click Delete Row:
    Delete every other row in Excel
  4. The above step has left you with an empty table, but don't worry, the "1" rows are still there. To make them visible again, simply remove auto-filter by clicking the Filter button again:
    Remove filter in Excel.
  5. The formula in column C recalculates for the remaining rows, but you don't need it anymore. You can now safely delete the Helper column:
    The Mod formula recalculates for the remaining rows.

As the result, only the even weeks are left in our worksheet, the odd weeks are gone!
Every other row is deleted.

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.

How to delete alternate rows in Excel with VBA

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:

Sub Delete_Alternate_Rows_Excel() Dim SourceRange As Range Set SourceRange = Application.Selection Set SourceRange = Application.InputBox("Range:", "Select the range", SourceRange.Address, Type:=8) If SourceRange.Rows.Count >= 2 Then Dim FirstCell As Range Dim RowIndex As Integer Application.ScreenUpdating = False For RowIndex = SourceRange.Rows.Count - (SourceRange.Rows.Count Mod 2) To 1 Step -2 Set FirstCell = SourceRange.Cells(RowIndex, 1) FirstCell.EntireRow.Delete Next Application.ScreenUpdating = True End If End Sub

How to delete every other row in Excel using the macro

Insert the macro in your worksheet in the usual way via the Visual Basic Editor:

  1. Press Alt + F11 to open the Visual Basic for Applications window.
  2. On the top menu bar, click Insert > Module, and paste the above macro in the Module
  3. Press the F5 key to run the macro.
  4. A dialog will pop up and prompt you to select a range. Select your table and click OK:
    Remove alternate rows in Excel using a macro.

Done! Every other row in the selected range is deleted:
Every other row in the selected range is deleted at once.

How to delete every Nth row in Excel

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:

MOD(ROW()-m, n)

Where:

  • m is the row number of the first cell with data minus 1
  • n is the Nth row you want to delete

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.):
Formula to identify every third row in Excel

And now, you perform the already familiar steps to filter "0" rows:

  1. Select any cell in your table and click the Filter button on the Data
  2. Filter the Helper column to show only "0" values.
  3. Select all of the visible "0" rows, right-click and choose Delete Row from the context menu.
  4. Remove the filter and delete the Helper column.

Every third row is deleted.

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

35 comments

  1. Thank you for your help and your support

  2. I have several Excel files that have data collected from automated test equipment. We got data at a rate about 10x higher than we need. The data files have 10x as many rows of data as we need.
    I would like to modify the data files to keep every 10th row of data and delete all of the other rows.
    I suspect there's a way to do this that is similar to what's presented in this article, but I'm not good enough at Excel to figure it out.
    Any suggestions?

    • I almost did it! (Delete 9 out of 10 rows).
      I created a column called "Filter" in the first row. Then I used Mod(Row()-1,10 ) to get values 0 through 9 assigned sequentially to each row.
      Then I used filtering so that every row labeled "0" was visible and all other rows (1 to 9) were hidden.
      I'm using Excel on a Mac, so my UI is different from what you show, but with a bit of fiddling I figured it out.
      The rows I want to remove were hidden, not deleted. So I followed your next step: Select the whole table of data and right click, then select "Delete Row".
      When I did that I got a spinning beach ball for a few minutes, then Excel crashed.
      The full data table has about 78,000 rows.
      I restarted Excel and recovered the file.
      In the recovered file, the results were still filtered (all "0" rows visible; and the "1 to 9" rows hidden). This time I selected the first two visible rows (not the entire data table), right clicked and selected "Delete Row". This deleted everything I selected. I.e., the "0" rows that I want to keep were deleted along with the "1 to 9" rows.
      Any suggestions?

  3. Got an error when trying the Macro. Here is where they say the error is...

    "For RowIndex = SourceRange.Rows.Count - (SourceRange.Rows.Count Mod 2) To 1 Step -2"

  4. thanks it will helps me alot

  5. Hi everyone,
    Just want to know that is there any why to delete all rows from each subtotal other than first 3 rows. Actually, need to select the sample based on first three rows from each subtotal and delete the rest of rows from subtotal so that count of each subtotal remains 3.

  6. the macro formula did not work. according to the comments, it's not working for anyone. delete that section and save future readers the time and frustration.

      • So I have tried the macro - it does work on small datasets apparently. Not sure of the size limit. I have datasets that are in excess of 60,000 lines and it basically spits an Overflow error. I'm interpreting this to mean there are too many lines of data for the macro to work? My entire dataset is currently over 100,000 lines. Any thoughts? Filtering works but takes 25-30 minutes during which I can't use Excel.

        • Hi! It may take some time for the macro to handle thousands of rows and the processing speed also depends on such things as the formatting of your data, other processes running at the moment, available system resources, RAM, etc.

  7. This worked, but in the opposite way that I intended. It keeps deleting the lines I want to keep and keeping the lines I want to get rid of. I've tried everything I could think of to make it work, I tried changing my row selections multiple times and I tried modifying the macro to Second row instead of First. It was the same result every time, and 3 hours later I'm exactly where I started.
    I'm going to have to agree with the other commenter that this was an utter waste of time.

    • I just tried the sorting method with 1s and 0s. Infinitely easier. Wish I could have gotten a handle on using the macro, but unfortunately it wasn't meant to be.

    • Hi Lauren,

      We have updated the code, and now it works exactly as it should - removes every other row in the selection. In fact, there was just one wrong line, which caused so many problems... Sorry for that.

  8. Perfect, thank you.

  9. Hi!
    Have you tried the ways described in this blog post? If they don’t work for you, then please describe your task in detail, I’ll try to suggest a solution.

  10. VERY GOOD, THANK YOU

  11. This was an EXCELLENT guide. Easy to follow and gives perfect results. Well done!

  12. Thanks

  13. Thank you! Very helpful.

  14. Thanks saved me alot of time with the macro

  15. So, this is how I am using this function. I export transcriptions from YouTube and end up with .csv data that populates each row in one column with data. I would rather have every transcription line separated by rows and columns. For instance: row(1,2,3,4,...) = column(1) "transcription line number", column(2) "transcription time stamp", column(3) "transcription text". I use =TRANSPOSE(A2:A3) to create each completed column, copy and paste values only in each new column, then use =MOD(ROW(),4) to parse the remaining rows.

  16. Thank you! This works equally well in Google Sheets!

  17. how to delete non Contiguous row with the help of row number, if i want to delete row 2 to 9 and row 15 to 36 and again row 42 to 75 then how i will delete the row? i know if row will delete then it will shift in upward direction and the required data will shift in another row so suggest me a VBA in which i defined start row to end row and then it will delete as per given row either i will give data 0 to 0 or may be 6 to 13.

  18. This was really helpful. In my case, I copied a table from a web page and ended up with all of the data in column one in Excel. So I added a few empty rows at the top and then copied the entire contents of column one into columns two and three, positioning the starting row up one each time so that the data was correct in every third row. Then I used your technique here to clear out the crud. Very nice!

  19. Nice idea

  20. Thank you so much, You saved me so much time

  21. Thank you
    It's very useful

  22. much help, 10/10.
    the mod command works great if you aren't used to vba!
    Thank you!

  23. Thank you so so much! You saved me so much time!

  24. you saved my lot of time and system space , thank you

  25. Thanks a lot. You really saved mytime.

  26. Thanks for the precious information..
    Will seek for other informations also.

  27. I love you so much for this!! You literally saved me hours.

  28. Very useful article, particularly 'How to delete alternate rows in Excel with VBA'.

    How do I add a form (and any additional code) that allows me to change the set 'alternate rows' to 'delete every Nth row or N Rows'. Other than going into the VBA editor For every new requirement.

    What I have in mind is say a button that on click calls a simple form that allows me to change the Nth Row or and N Rows parameters or confirm the last entered values. Press OK, Then Runs the dialog that prompt me to select a range.

    Thanks

    also 'How to delete alternate rows in Excel with VBA' might be better at the end of the Article so that all the 'filtering' non VBA subjects were together.

Post a comment



Thank you for your comment!
When posting a question, please be very clear and concise. This will help us provide a quick and relevant solution to
your query. We cannot guarantee that we will answer every question, but we'll do our best :)