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?
 

Some videos you may like

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.

tactps

Well-known Member
Joined
Jan 20, 2004
Messages
3,459
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
 

Watch MrExcel Video

Forum statistics

Threads
1,123,303
Messages
5,600,857
Members
414,407
Latest member
Zaner0445

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