delete 3 rows after every 2 rows

%3CLINGO-SUB%20id%3D%22lingo-sub-1956183%22%20slang%3D%22en-US%22%3Edelete%203%20rows%20after%20every%202%20rows%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1956183%22%20slang%3D%22en-US%22%3E%3CP%3EHello%20Everyone%2C%3C%2FP%3E%3CP%3E%3CSPAN%3EI%20have%20a%20data%20and%20trying%20to%20delete%203%20rows%20after%20every%202%20rows.%20How%20can%20I%20do%20it%20as%20deleting%20them%3F%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3EBecause%20manually%20takes%20too%20long.%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3ELike%20-%26nbsp%3B%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Screenshot%20(729).png%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F237717iE632F0601D6390EE%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22Screenshot%20(729).png%22%20alt%3D%22Screenshot%20(729).png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1956183%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1956249%22%20slang%3D%22en-US%22%3ERe%3A%20delete%203%20rows%20after%20every%202%20rows%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1956249%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F811137%22%20target%3D%22_blank%22%3E%40Zan_Hanifee%3C%2FA%3E%26nbsp%3BHere's%20a%20quick-and-dirty%20way%20to%20do%20it.%3C%2FP%3E%3CP%3EAdd%20a%20helper%20columns%20and%20enter%20this%20formula%20on%20the%20first%20row%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DMOD(ROW()%2C5)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3ECopy%20it%20down%20as%20far%20as%20needed%20and%20put%20a%20filter%20of%20the%20table.%20Filter%20the%20helper%20column%20to%20show%20only%20items%202%20and%203.%20Copy%20and%20past%20all%20to%20a%20new%20table.%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Screenshot%202020-12-04%20at%2015.30.18.png%22%20style%3D%22width%3A%20310px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F237722i6DA7F65B1AE401D0%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22Screenshot%202020-12-04%20at%2015.30.18.png%22%20alt%3D%22Screenshot%202020-12-04%20at%2015.30.18.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
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

@Zan_Hanifee 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 @Zan_Hanifee,

 

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/)...