Forum Discussion
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"
3 Replies
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.
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
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.
