Delete Every Other Row or Column in Excel (using Formula or VBA)

In this video, I will show you various ways to delete every other row or delete every other column in Excel.

I will also cover how to delete every third/fourth/fifth row or column in Excel.

To delete alternate rows, you can use a helper column and use a formula that helps you identify alternate rows. This could be done by using the ISEVEN function that checks each row number and returns TRUE if the row is even and false if it isn’t.

Once you have this, you can easily filter the rows with FALSE in the helper columns and delete these.

In case you want to delete every third row, you need to use the MOD function to identify every third row. Once you have it, you can easily filter and delete every third row or every fourth row.

I also cover a method to use a simple VBA code to delete alternate rows in Excel.

Below is that VBA CODE TO DELETE ALTERNATE ROWS:

Sub Delete_Every_Other_Row()
Dim Rng As Range
Set Rng = Application.InputBox(“Select the Range (Excluding headers)”, “Range Selection”, Type:=8)
For i = Rng.Rows.Count To 1 Step -2
If i Mod 2 = 0 Then
Rng.Rows(i).Delete
End If
Next i
End Sub

When it comes to deleting alternate columns, you can not filter these. But you can sort and bring all those columns together that you want to delete.

I cover a simple method that uses the MOD function to identify alternate columns and then sort these from left-to-right to bring these together. Once you have these in one place, you can select and delete these.

And there is also a VBA code that you can use to delete alternate columns.

Below is the VBA CODE TO DELETE ALTERNATE COLUMNS

Sub Delete_Every_Other_Column()
Dim Rng As Range
Set Rng = Application.InputBox(“Select the Range (Excluding headers)”, “Range Selection”, Type:=8)
For i = Rng.Columns.Count To 1 Step -2
If i Mod 2 = 0 Then
Rng.Columns(i).Delete
End If
Next i
End Sub

You can also read more about this method here: https://ift.tt/2UpKlf3

Subscribe to this YouTube channel to get updates on Excel Tips and Excel Tutorials videos – https://www.youtube.com/c/trumpexcel

This YouTube channel is managed by Sumit Bansal (who also runs the TrumpExcel website). This channel is meant for people who want to learn Excel. It covers a lot of Excel basics and advanced Excel topics such as Excel Formulas, Functions, Pivot Table, Shortcuts, Excel VBA, Macros, Excel Dashboards, Excel Charts, Conditional Formatting, Power Query, etc. Please subscribe to this channel to be the first to know when new Excel tutorials come out.

You can find a lot of useful Excel resources on the following site: https://trumpexcel.com/

#Excel #ExcelTips #ExcelTutorial

View on YouTube