Delete rows if a specific value is returned

[yabey]

I need a formula that finds a value or range of values in a list and if the value meets the criteria automaticaly delete that row. For example I want to search B3:B7 for a 0 value if I find a cell in that range with a 0 value i want the formula to delete the entire row.

Is this possible?

 

[tactps]

Welcome!

How do you define what range to look at and what data to look for? For example, are you going to type in the reference for the range into a cell, or do you want this process to ask you for the range?

It is generally worthwhile using Colo's HTML Editor to show an sample of your spreadsheet (see the bottom of the forum to download), or at least as much detail as you can give to explain.

 

[Yogi Anand]

Hi yabey:

Welcome to MrExcel Board!

Let us look at the following illustration ...

Book2
	A	B	C	D
3		5	0
4		2	0
5		0	#VALUE!
6		1	0
7		0	#VALUE!
Sheet1

Cell B3:B7 shows your data. Now to locate the 0 entries to be deleted I have used the following formula

in cell C3 ... =--(IF(B3<>0,0,""))

and I copied this down to cells C4:C7

Then I selected cells C3:C7, and then I did

EDIT|Goto|Special|Formulas|Errors

and the cells C5 and C7 were selected.

Then I did Edit|Delete|Entire row

I hope this helps!

 

[yabey]

That work perfect. Thank you

 

[francoisblake]

Good day Yogi
This formula works perfectly
But I have never heard of a =--if( formual
If possible, please can you explain this
Thank you