Forum Discussion

EdChicago's avatar
EdChicago
Copper Contributor
Sep 25, 2019

Excel: Delete Row action in a formula?

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"    
excel
Formulas and Functions
Subodh_Tiwari_sktneer's avatar
Subodh_Tiwari_sktneer
Silver Contributor
Sep 25, 2019

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.

  • EdChicago's avatar
    EdChicago
    Copper Contributor
    Sep 25, 2019

    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 Attached

    • Subodh_Tiwari_sktneer's avatar
      Subodh_Tiwari_sktneer
      Silver Contributor
      Sep 26, 2019

      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.

       

Resources