Sometimes you may have a need to delete every other row (i.e., alternate rows), or every third/fourth/fifth row in Excel. One use-case of this could be when you have weekly data and you only want the data for even weeks or odd weeks. Another could be when you get a data dump from a database and all the useful data is only in every second or third row. While you always have the option to manually select and delete rows, this is inefficient in case you have a large dataset. There are better ways to do this. In this tutorial, I will show you a couple of ways to delete every other row in Excel using a simple filtering technique. And if you’re cool with using a VBA macro code, I have also given a short VBA code that does it with a single click. The methods are shown in this tutorial work for all the versions of Excel (2007, 2010, 2013, and 2016)
Delete Every Other Row By Filtering the Dataset (Using Formula)
If you could somehow filter all the even rows or the odd rows, it would be super easy to delete these rows/records. While there is no inbuilt feature to do this, you can use a helper column to first divide the rows in to odd and even, and then filter based on the helper column value. Suppose you have a dataset as shown below, which has sales data for each sales rep for two regions (the US and Canada) and you want to delete the data for Canada.
Below are the steps to first filter and then delete every other row (which has data for Canada): The above steps filter every second row in the dataset and then deletes those rows.
Don’t worry about the helper column values at this stage. The resulting data has only the rows for the US and all the Canada rows are deleted. You can delete the helper column now. In case you want to delete every other row starting from the first row onwards, select the TRUE option in step 7 and deselect the FALSE option. While the above method is great, it has two drawbacks:
Delete Every Nth Row By Filtering the Dataset (Using Formula)
In the above method, I showed you how to delete every other row (alternate row) in Excel. And you can use the same logic to delete every third or fourth row in Excel as well. Suppose you have a dataset as shown below and you want to delete all every third row.
The steps to delete every third row are almost the same as the one covered in the above section (for deleting alternate rows). The only difference is in the formula used in Step 2. Below are the steps to do this: The above steps would delete every third row from the dataset and you would get the resulting data as shown below.
Don’t worry about the helper column values at this stage. You can delete the helper column now. The formula used in Step 2 uses the MOD function – which gives the remainder when one number is divided by the other. Here I have used the ROW function to get the row number and it’s divided by 3 (because we want to delete every third row). Since our dataset starts from the second row onwards (i.e., row #2 contains the first record), I use the following formula:=MOD(ROW(),3)=1 I am equating it to 1 and for every third row, the MOD formula will give the remainder as 1. You can adjust the formula accordingly. For example, if your dataset’s first record starts from third-row onwards, the formula would be =MOD(ROW(),3)=2
Automatically Delete Every Other Row (or Nth row) using VBA Macro (Quick Method)
If you have to delete every other row (or every nth row) quite often, you can use a VBA code and have it available in ṭhe Quick Access Toolbar. This will allow you to quickly delete alternate rows with a single click. Below is the code that first asks you to select the range in which you want to delete alternate rows, and then delete every second row in the selected dataset. When you run the above code, it will first ask you to select the range of cells. Once you have made the selection, it will go through each row and delete every second row. If you want to delete every third row instead, you can use the below code: Where to put this VBA macro code? You need to place this code in a regular module in the VB editor in Excel. Below are the steps to open the Vb Editor, add a module and place the code in it: Once you have the code in the VB Editor, you can use the following methods to run the code:
Run a Macro from the VB Editor (by clicking on the green play button in VB Editor toolbar) By placing the cursor on any line in the code and using the F5 key By assigning the macro to a button/shape By adding the macro to the Quick Access toolbar
If you have to do this often, you can consider adding the VBA code to the Personal Macro Workbook. This way, it will always be available to you for use in all the workbooks. When you have a VBA code in a workbook, you need to save it as a macro-enabled filed (with the .XLSM extension)
Delete Every Other Column (or Every Nth Column)
Deleting every alternate row or every third/fourth row is made easy as you can use the filter option. All you have to do is use a formula that identifies alternate rows (or every third/fourth row) and filters these rows. Unfortunately, this same methodology won’t work with columns – as you can not filter columns the way you filter rows. So, if you have to delete every other column (or every third/fourth/Nth column), you need to be a little more creative. In this section, I will show you two methods you can use to delete every other column in Excel (and you can use the same method to delete every third/fourth/Nth column if you want).
Delete Alternate Columns Using Formulas and Sort Method
Suppose you have a dataset as shown below and you want to delete every other column (excluding the header column A)
The trick here would be to identify alternate columns using a formula and then sort the columns based on that formula. Once you have the sorted columns together, you can manually select and delete this. Below are the steps to delete every other column in Excel: The above steps sort all the columns and bring all the alternate columns together at one place (at the end). Now you can select all these columns (for which the formula value is 1), and delete these. Although this is not the best solution, it still gets the work done. In case you want to delete every third column or every fourth column, you need to adjust the formula accordingly.
Delete Alternate Columns Using VBA
Another quick way to delete alternate columns is to use the below VBA code: The above code asks you to select a range of cells which has the columns. Here you need to select the columns excluding the one which has the header. Once you have specified the range of cells with the data, it will use a For Loop and delete every other column. In case you want to delete every third column, you can use the code below (and adjust accordingly to delete the Nth column) The steps on where to place this VBA code and how to use it covered in the above section titled – “Automatically Delete Every Other Row (or Nth row) using VBA Macro (Quick Method)” Hope you found this Excel tutorial useful. You may also like the following Excel tutorials:
Insert a Blank Row after Every Row in Excel (or Every Nth Row) Highlight Rows Based on a Cell Value in Excel (Conditional Formatting) Number Rows in Excel (Quick and Easy ways) Delete Blank Rows in Excel (with and without VBA) How to Insert Multiple Rows in Excel How to Split Multiple Lines in a Cell into a Separate Cells / Columns How to Merge Cells in Excel
