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:
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 SubWhen 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
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 SubSubscribe 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
Paste – If you would like to paste your data as is, with no changes, right click and select this icon.
Paste Values – If you’re copying a formula, but only want to paste the results, select this icon, with the numbers.
Paste Formula – Say you wanted to paste just a formula or set of formulas, you would choose the option with the “fx” in the icon. Note that depending on what the formula is doing, the result may be different from the original.
Paste and Transpose – The transpose option allows you to change the orientation of your data. Let’s say you had four items listed vertically. Using the transpose option would allow you to paste the items horizontally, or vice versa.
Paste Formatting – Excel gives you the option of pasting the formatting only. If you had some nice shading, or conditional formatting and wanted to copy that, but not modify the formula or data of the target cell or range, then paste formatting would be the option to choose.
Paste Link – You can simply paste a link to, or reference to the source as a formula or formulas if you are copying multiple cells. Hint: If you are copying only one cell, by default, the reference will be absolute. This means that you can move the formula around, but the cell(s) it is pointing to will not change. When copying multiple cells, the result will be relative references.