Today we are going to explore various ways to delete every other row or delete every other column in Excel. VBA macro code is included to help you on your way if that is the path you choose.
Additionally, we 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:
|
1 2 3 4 5 6 7 8 9 |
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 cannot filter these. You can instead 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
|
1 2 3 4 5 6 7 8 9 |
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 |
Subscribe to this YouTube channel to get updates on Excel Tips and Excel Tutorials videos – https://www.youtube.com/c/trumpexcel
You can find a lot of useful Excel resources on the following site: https://trumpexcel.com/
#Excel #ExcelTips #ExcelTutorial