There are lots of instances where you would like to delete every second, third, fifth, etc. rows in your worksheet. One instance that readily comes to mind is when you use data from another source.
Luckily this is not a difficult task!
Follow the steps below to learn how:
- Open your worksheet in which you need to delete rows.
- Insert two columns on the far left-hand side of your worksheet (A and B).
- In your new column A, use AutoFill to sequentially number your rows from 1 to the end.
- In B1, enter the formula =MODA1,2).
- Copy that formula down to all cells in the column.
- Click on the Data tab of your Ribbon
- In the Sort & Filter group, select the Filter tool.
- A small drop-down list will appear at the top of each column in your worksheet.
- Using the drop-down arrow in column B, select the zero. Now, only those rows with a zero in the column B appear.
- Select all the displayed rows and delete them.
- Turn off AutoFilter and delete columns A and B.
The formula in step 4 can be changed substituting another number than 2, depending on whatever multiple of rows you would like to delete.
Versions 2007, 2010