Home

Excel: Delete Row action in a formula?

%3CLINGO-SUB%20id%3D%22lingo-sub-874954%22%20slang%3D%22en-US%22%3EExcel%3A%20Delete%20Row%20action%20in%20a%20formula%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-874954%22%20slang%3D%22en-US%22%3E%3CP%3EIs%20there%20an%20action%20if%20formulas%20to%20delete%20rows%3F%20So%20if%20the%20outcome%20of%20the%20formula%20isn't%20a%20certain%20value%2C%20the%20entire%20row%20will%20be%20deleted.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EFor%20Example%2C%20If%20A2%3DYes%2C%20%22True%22%2C%20%22delete%20row%22%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-874954%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-875173%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%3A%20Delete%20Row%20action%20in%20a%20formula%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-875173%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F415082%22%20target%3D%22_blank%22%3E%40EdChicago%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EFormulas%20cannot%20delete%20the%20rows.%20You%20will%20need%20a%20macro%20to%20achieve%20this.%3C%2FP%3E%3CP%3EIf%20you%20are%20open%20to%20a%20VBA%20solution%2C%20please%20provide%20more%20details%20and%20explain%20what%20are%20you%20trying%20to%20achieve.%20If%20required%2C%20please%20upload%20a%20sample%20file%20to%20show%20what%20you%20have%20and%20what%20is%20the%20end%20result%20you%20are%20trying%20to%20achieve.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-875401%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%3A%20Delete%20Row%20action%20in%20a%20formula%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-875401%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F394231%22%20target%3D%22_blank%22%3E%40Subodh_Tiwari_sktneer%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EOn%20Sheet%201%20I%20have%20a%20question%20in%20column%20A%20%26amp%3B%20I%20have%20Yes%20%26amp%3B%20No%20drop-downs%20in%20both%20columns%20B%26amp%3BC.%20On%20Sheet%202%20I'm%20looking%20for%20a%20formula%20where%20if%20the%20answer%20is%20Yes%20in%20BOTH%20boxes%2C%20the%20information%20in%20column%20A%20from%20sheet%201%20will%20be%20displayed.%20However%2C%20if%20the%20answer%20to%20either%20question%20is%20%22No%2C%22%20I%20would%20like%20the%20whole%20row%20deleted.%20I%20would%20then%20have%20a%20formula%20for%20Yes%2FNo%20responses%2C%20No%2FYes%20responses%20%26amp%3B%20No%2FNo%20responses.%3C%2FP%3E%3CP%3EExample%20Attached%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F133528i103D8D86258BC867%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22Sheet%201.jpg%22%20title%3D%22Sheet%201.jpg%22%20%2F%3E%3C%2FSPAN%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F133529i1B4F62FB71E6858C%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22Sheet%202.jpg%22%20title%3D%22Sheet%202.jpg%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-876137%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%3A%20Delete%20Row%20action%20in%20a%20formula%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-876137%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F415082%22%20target%3D%22_blank%22%3E%40EdChicago%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYou%20may%20try%20this%20%3CSTRONG%3EArray%20Formula%3C%2FSTRONG%3Ewhich%20requires%20confirmation%20with%20%3CSTRONG%3ECtrl%2BShift%2BEnter%3C%2FSTRONG%3Einstead%20of%20Enter%20alone.%3C%2FP%3E%3CP%3EOn%20Sheet2%3C%2FP%3E%3CP%3EIn%20A2%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3E%3DIFERROR(INDEX(Sheet1!A2%3AA50%2CSMALL(IF(Sheet1!%24B%242%3A%24B%2450%26amp%3BSheet1!%24C%242%3A%24C%2450%3D%22YesYes%22%2CROW(Sheet1!%24A%242%3A%24A%2450)-ROW(Sheet1!%24A%242)%2B1)%2CROWS(A%242%3AA2)))%2C%22%22)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3EConfirm%20with%26nbsp%3B%3CSTRONG%3ECtrl%2BShift%2BEnter%3C%2FSTRONG%3E%20and%20copy%20it%20down%20across%20to%20column%20C.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESame%20way%20you%20can%20create%20the%20formulas%20for%20different%20combinations%20of%20Yes%20and%20No.%3C%2FP%3E%3CP%3ERefer%20to%20the%20attached%20for%20more%20details.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
EdChicago
New Contributor

Is there an action if formulas to delete rows? So if the outcome of the formula isn't a certain value, the entire row will be deleted.

 

For Example, If A2=Yes, "True", "delete row"

 

 

3 Replies

@EdChicago 

Formulas cannot delete the rows. You will need a macro to achieve this.

If you are open to a VBA solution, please provide more details and explain what are you trying to achieve. If required, please upload a sample file to show what you have and what is the end result you are trying to achieve.

@Subodh_Tiwari_sktneer 

 

On Sheet 1 I have a question in column A & I have Yes & No drop-downs in both columns B&C. On Sheet 2 I'm looking for a formula where if the answer is Yes in BOTH boxes, the information in column A from sheet 1 will be displayed. However, if the answer to either question is "No," I would like the whole row deleted. I would then have a formula for Yes/No responses, No/Yes responses & No/No responses.

Example AttachedSheet 1.jpgSheet 2.jpg

@EdChicago 

You may try this Array Formula which requires confirmation with Ctrl+Shift+Enter instead of Enter alone.

On Sheet2

In A2

=IFERROR(INDEX(Sheet1!A2:A50,SMALL(IF(Sheet1!$B$2:$B$50&Sheet1!$C$2:$C$50="YesYes",ROW(Sheet1!$A$2:$A$50)-ROW(Sheet1!$A$2)+1),ROWS(A$2:A2))),"")

Confirm with Ctrl+Shift+Enter and copy it down across to column C.

 

Same way you can create the formulas for different combinations of Yes and No.

Refer to the attached for more details.

 

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
22 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
cntvertex in Discussions on
13 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies