How to Delete Alternate Rows in Excel (5 Quick Ways)

You may face many situations when you need to delete alternate rows in Excel worksheets. For example, you may want to keep data for odd rows and move all data of even rows. We can do it manually but that is not efficient for a large worksheet. So I’ll show 5 quick and suitable methods in this article to delete alternate rows in Excel with sharp steps and vivid illustrations.


Download Practice Workbook

You can download the free Excel template from here and practice on your own.


5 Ways to Delete Alternate Rows in Excel

Firstly, I’ll introduce you to my dataset which contains some salespersons’ sales in two regions for the same month. Now we’ll apply five efficient methods to remove the alternate rows which means we’ll remove the rows which contain the UK region.


Method 1: Use Excel Flash Fill and Filter to Delete Alternate Rows

In our very first method, I’ll use Excel Flash Fill and Filter option to remove the alternate rows. For that, I have added a helper column.

Excel Flash Fill And Filter to Delete Alternate Rows

Steps:

  • Type TRUE in the first column and FALSE in the second column in the data table.

Excel Flash Fill And Filter to Delete Alternate Rows

  • Then select those two cells and drag down the Flash Fill

Excel Flash Fill And Filter to Delete Alternate Rows

Now all the cells are filled with that pattern.

Excel Flash Fill And Filter to Delete Alternate Rows

  • Later select any cell of the dataset and click as follows-
    Home > Editing > Sort & Filter > Filter.

Excel Flash Fill And Filter to Delete Alternate Rows

Now take a look that the filter option is added in the headers.

  • Click the filter icon in the Helper column.
  • Then mark only the FALSE option.
  • Press OK.

Excel Flash Fill And Filter to Delete Alternate Rows

Now it is showing the rows of the UK region only.

  • Select the rows and hit the Delete button on your keyboard.

The rows are deleted now.

  • Now to get back the other rows click the filter icon again.
  • Then mark the TRUE option only.
  • Finally, just press OK.

Here are our remained rows.

Read More: How to Filter and Delete Rows with VBA in Excel (2 Methods)


Method 2: Insert Cell Format And Filter to Erase Alternate Rows

We can use the cell Format and Filter options together to remove alternate rows. We’ll use the Excel table format here.

Steps:

  • Select the whale dataset.
  • Then click as follows- Home > Format as Table.
  • Later, select a table format that has fill color and no fill alternatively between rows.

Then a dialog box will open up which will show the data range. Make sure that the My table has headers option is marked if you select the data range with headers.

  • Press OK.

Cell Format And Filter to Erase Alternate Rows

 

  • After that click successively- Design > Convert to Range.

Cell Format And Filter to Erase Alternate Rows

  • Then again click as follows-
    Home > Editing > Sort & Filter > Filter.

  • Now press any of the headers’ filter icons and click- Filter by Color > No Fill.

Now you will observe that not filled cells are filtered which contain UK regions.

  • At this moment just select the rows and press Delete on your keyboard.

Cell Format And Filter to Erase Alternate Rows

  • Now to get back the other rows just turn off the Filter option by clicking again-
    Home > Editing > Sort & Filter > Filter.

Cell Format And Filter to Erase Alternate Rows

We have got back the other rows now.

Cell Format And Filter to Erase Alternate Rows

Read More: Delete an Excel Row If a Cell Contains Specific Values (3 Methods)


Method 3: Use MOD And ROW Functions to Delete Alternate Rows in Excel

Using functions is very user-friendly in Excel. We can perform the task by using the MOD and ROW functions. The MOD function is used to return the remainder of two numbers after division and the ROW function is used to return the row number of a cell. Here we’ll need a helper column again.

Steps:

  • Type the following formula in Cell F5
=MOD(ROW(),2)
  • Hit the Enter

  • Drag down the Fill Handle icon to copy the formula.

MOD And ROW Functions to Delete Alternate Rows in Excel

The helper column is filled now.

MOD And ROW Functions to Delete Alternate Rows in Excel

  • Then select any cell and click as follows to active filter option-
    Home > Editing > Sort & Filter > Filter.

MOD And ROW Functions to Delete Alternate Rows in Excel

  • Later, click the filter icon of the Helper column.
  • Put a mark on the ‘0’ option.
  • Then press OK.

  • After that just select those filtered rows and hit the Delete button on your keyboard.

The rows are removed now.

  • To get back the other rows just press the filter icon again and put a mark on the ‘1’ option.
  • Finally, press OK.

Here’s our output.

⏬ Formula Breakdown:

➥ ROW()
The ROW function will return the row number of that cell which is-
{5}

➥ MOD(ROW(),2)
Then the MOD function will find the remainder after dividing the row number by 2 and that will return as-
{1}

Read More: Excel Delete Rows in a Range with VBA (3 Easy Ways)


Similar Readings:


Method 4: Apply Excel ISEVEN And ROW Functions to Remove Alternate Rows

We can use another combination of functions to do the operation they are the ISEVEN and ROW functions. The ISEVEN function is used to check whether a number is even or not.

Steps:

  • In Cell F5 write the following formula-
=ISEVEN(ROW())
  • Hit the Enter

ISEVEN And ROW Functions to Remove Alternate Rows

  • Then drag down the Fill Handle icon to copy the formula for the other cells.

ISEVEN And ROW Functions to Remove Alternate Rows

  • After that select any cell and click as follows to active filter option- Home > Editing > Sort & Filter > Filter.

ISEVEN And ROW Functions to Remove Alternate Rows

  • Then click the filter icon of the Helper column.
  • Put a mark on the ‘TRUE’ option.
  • Press OK.

ISEVEN And ROW Functions to Remove Alternate Rows

  • Later, just select those filtered rows and hit the Delete button on your keyboard to remove them.

  • Now to get back the other rows just press the filter icon again and put a mark on the ‘FALSE’ option.
  • Finally, just press OK.

The rows with the UK region are deleted now.

⏬ Formula Breakdown:

➥ ROW()
The ROW function will return the row number of that cell which is-
{5}

➥ ISEVEN(ROW())
Then the ISEVEN function will check whether the row number is even or not and it will return as-
{FALSE}

Read More: How to Delete Rows in Excel without Affecting Formulas (2 Quick Ways)


Method 5: Use VBA Macro to Remove Alternate Rows in Excel

In our last method, I’ll show how we can do the operation using VBA Macros. It has fewer steps and is a very fast method.

Steps:

  • Right-click on the sheet title.
  • Select View Code from the context menu.

A VBA window will appear.

VBA Macros in Excel to Remove Alternate Rows

  • Write down the following codes in it-
Sub Delete_Alternate_Rows()
Dim x As Range
Set x = Application.InputBox("Select the Range Without Headers", "Range Selection", Type:=8)
For i = x.Rows.Count To 1 Step -2
If i Mod 2 = 0 Then
x.Rows(i).Delete
End If
Next i
End Sub
  • Then press the Play icon to run the codes.

A dialog box will appear to select the data range.

VBA Macros in Excel to Remove Alternate Rows

Now set the data range and press OK.

And we are now done with deleting alternate rows.

Read More: Macro to Delete Row in Excel If Cell is Blank


Conclusion

I hope all of the methods described above will be good enough to delete alternate rows in Excel. Feel free to ask any question in the comment section and please give me feedback.


Related Articles

Mithun

Mithun

Hello! I am Md. Sourov Hossain Mithun. Welcome to my profile. Currently, I am working at Exceldemy as an Excel and VBA Content Developer. Excel is amazing software. Here I will post excel related useful articles. I am a graduate of Bangladesh University of Engineering and Technology. I love to learn new things and work with them. Thank you.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo