delete 3 rows after every 2 rows

Iron Contributor

Hello Everyone,

I have a data and trying to delete 3 rows after every 2 rows. How can I do it as deleting them?

Because manually takes too long.

Like - 

Screenshot (729).png

2 Replies

@Excel Here's a quick-and-dirty way to do it.

Add a helper columns and enter this formula on the first row

=MOD(ROW(),5)

Copy it down as far as needed and put a filter of the table. Filter the helper column to show only items 2 and 3. Copy and past all to a new table.

Screenshot 2020-12-04 at 15.30.18.png

 

Hey @Excel,

 

I think I found a solution for you, as long as the pattern remains keep 2, delete 3.

 

  • Add a column to designate which rows to keep or delete
  • Enter "keep" or "delete" in the pattern you want down the column next to the rows you want to keep or delete (i.e. keep 2, delete 3 = keep, keep, delete, delete, delete)
  • Highlight the pattern and drag it down the list to populate every 2 rows with keep followed by 3 rows with delete
  • Sort or filter by keep/delete to remove unwanted rows. You can go to Data > filter to filter to just show the rows marked as keep OR you can sort by the keep/delete column and just delete every row marked as delete.
 

Highlight the pattern and drag it down the list to populate every 2 rows with keep followed by 3 rows with delete:

Excel - Keep-Delete - highlight & drag pattern.png

Excel - Keep-Delete.png

 

I also created a video for this if that makes it easier to understand: https://youtu.be/_sLbzkN4veg 

 

I hope this resolved your question!

In this video, I show you how to delete a pattern of rows in Excel. This was a request in the Microsoft Tech Community (https://techcommunity.microsoft.com/) asking how to automate deleting 3 rows after every 2 rows. This was my solution. -Add a column to designate which rows to keep or delete ...