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

⚙️ Gear I Recommend:

Note: Some of these links here are affiliate links!

#Excel #ExcelTips #ExcelTutorial

[UpExcel]

Can't believe I've never noticed you can sort by column as well as row in Excel - thanks for the info. Great idea to use the MOD formula to come up with the number sequence too.

[tmmash]

Thank you for this video. It helped me clear blank rows. I used the ISBLANK function to identify the blank cells and BOOM! Worked like a charm. Thanks again.

[wayneedmondson]

Hi Sumit.. great video. Thanks for the useful tips and also the VBA code. Thumbs up!!

[JWouters]

Awesome video. By far the best on the topic. Thanks!

[rina]

Thank you, this will be very helpful as i get a large data with duplicates.

[Altobot-oy]

Thank you so much you saved me so much time

[davidcollier]

Really excellent video, thanks very much.

[brajamohansahoo]

Kindly make a video for inserting a row after every even row

[rasedul_islam_rashed]

Nice to see the application of the formula. But before seeing this I used to create a column in just left of my data range and then manually put the value "YES, NO". Then double click on the cell below corner to make a copy of these "two cell values" up to the end of my desired range column. After then I could easily apply DATA FILTER and select YES to get my desired rows.

If I need to delete every 3rd row, then I create and copy "YES, NO, NO". Then drag these three cell values up to the end. After then you can easily apply DATA FILTER. Why would I memorize the critical formula again and again;)

[dmtmenlen]

Thxx in abundance....your vdo tutorial is very useful for my jobbb :)

[markdayphotography]

Could you make a video on how to create a formula that references data in alternate cells (this will most likely be helpful when referring to VLookUP tables). Many thanks.

[abutahir]

How to delete rows containing specific names? For example, how to delete rows having John and Xin?

[jas-hysy]

hi, only if I select 4 columns, does it execute the code. otherwise, it doesn't. My range in a couple hundred