Delete rows if a specific value is returned

yabey

New Member
Joined
Feb 23, 2004
Messages
7
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?
 

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney

tactps

Well-known Member
Joined
Jan 20, 2004
Messages
3,460
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.
:biggrin:
 

Yogi Anand

MrExcel MVP
Joined
Mar 12, 2002
Messages
11,454
Hi yabey:

Welcome to MrExcel Board!

Let us look at the following illustration ...
Book2
ABCD
350
420
50#VALUE!
610
70#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!
 

francoisblake

New Member
Joined
Sep 6, 2012
Messages
6
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
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,167,811
Messages
5,855,775
Members
431,763
Latest member
AlexisChch

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top