How to Remove Every Other Row in Excel – Deleting Every Nth Row

  • Home
  • / How to Remove Every Other Row in Excel – Deleting Every Nth Row

Deleting or adding something in Excel spreadsheets is not rocket science but it is tricky when you need to do it manually. Though it just takes a few clicks of simply highlighting the rows or columns and right-clicking to select the Delete option, however, it never always incites users. Just because of its time consuming nature, users tend to seek less complex ways to remove every other row in Excel.

People think it just takes you time to select rows to delete and then press the delete button. Let’s see if it is a real-time process or not.

Today, you will get to know how to remove every other row in Excel by using a few simple-to-follow approaches.

How to Remove Every Other Row in Excel with Cell Format and Filter?

Here you will see how cell Format and Filter options mutually help in deleting every other row. Let’s follow the steps given below:

  • Select the dataset.
  • Open the Home tab.
  • Select the Format as a Table option.
  • Choose a table format in which the fill color is added between rows.

  • A dialog box of data range will pop up.
  • Check to see if the My table headers option is marked or not. If it is not marked, check the option with headers.
  • Press OK.

  • Select the Design option and then click on Convert to Range.

  • Open the Home tab once again.
  • Select the Editing menu and choose Sort & Filter option.
  • From there, click on the Filter option.

  • Click Filter by Color and then the No Fill option once you press the headers filter icon.

  • You will notice the cell that is not filled are filtered. Select the rows and press the Delete button on the keyboard.

  • Turn off the Filter menu to get back to other rows. For this, open the Home tab.
  • Choose the Editing menu and select Sort & Filter option.
  • Now, choose Filter.

Now, you will see all the other rows are back.

Price banner Earn and Excel

How to Remove Every Other Row in Excel with a Helper Column?

Apart from the above methods, this approach uses a helper column to remove every other row. This process is also useful to copy every nth row.

Enter TRUE in the first row and FALSE in the row under it in an empty column next to the data.

TRUE and FALSE must be highlighted and then drag the cursor to the last row.

For each row, copy TRUE and FALSE. Release the mouse to copy both of them.

From the Ribbon, choose the Home tab and click on the Editing menu.

From there choose Sort & Filter menu and then click on the Filter option.

You will see an arrow beside the header of the new column. Click on it and choose either TRUE or FALSE.

Press OK to filter the data.

Now, highlight the filtered data in front of you.

Press the DELETE key from the keyboard.

Display the whole table by removing the filter. Delete the helper column as well to highlight the rest of the data.

From the Ribbon, open the Home tab and choose the Editing menu.

Click on the Sort & Filter option and then choose the Smallest to Largest option.

You will instantly notice all the empty rows have now vanished from the sheet.

How to Remove Every Other Row in Excel with VBA

Last but not the least, here comes the VBA approach that somehow has become the favorite of the majority of Excel users.

The above-mentioned methods are worthy enough to try, however, if you don’t want to spend time trying them, let’s create an automated code for deleting every other row in Excel sheets. Below is the macro code you can use to automate the process:

Sub 

Delete_Alternate_Rows_Excel() 

Dim Source

Range As Range Set Source

Range = Application.Selection Set Source

Range = Application.InputBox("Range:", "Select the range", SourceRange.Address, Type:=8) If Source

Range.Rows.Count >= 2 Then Dim First

Cell As Range Dim RowIndex As Integer Application.Screen

Updating = False For RowIndex = Source

Range.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 the row with macro?

The above macro code needs to be added to the sheet through Visual Basic Editor. Let’s follow the steps given here:

  • Press ALT + F11 key command to open the Visual Basic Editor.
  • Click on the Insert tab and choose the Module menu.
  • Paste the above code in the Module.
  • To run the code, you need to press the F5 key.
  • You will see a dialog box in which you need to select a range.
  • Click OK once you have selected the table.

That’s it!

From the selected range, you will notice that every other row is deleted:

Let’s Wrap Up:

Now you have learned how to remove every other row in Excel by using multiple methods. The above-mentioned methods are fully tried and tested that’s why you don’t need to hesitate. You will always get authentic outcomes whichever method you apply.

Price banner Earn and Excel

Write your comment Here