Delete cells with a specific value

**agupta** · 05-20-2011, 01:50 PM

Hi,

I am not a new user of Excel but i think this would require use of a macro which i am new to. Probably this wud be very straightforward for you, but i wanted to delete cells containing values "0" and "1" in all the columns. Could you please help me with that.

Thanks,
Aman

**ChemistB** · 05-20-2011, 02:24 PM

Do you want to clear them or delete them? If you delete them, which way do you want your spreadsheet to shift (to the left or up)?

**agupta** · 05-20-2011, 02:28 PM

Thanks for the reply chemist! I want to delete the cells and shift the the spreadsheet up.Thanks for your help.

**ChemistB** · 05-20-2011, 03:33 PM

Try this macro

Please Login or Register  to view this content.

Does that work for you?

**agupta** · 05-20-2011, 04:39 PM

I tried the code, but strangely it deleted some of them but not all!

**agupta** · 05-20-2011, 04:50 PM

i have all the values in the cells with two decimal places like 0.45, 0.69, 0.00,1.00. Wud that make a difference. Is that why it deletes some cells and not others. I also notice that it has deleted some of the cells other then 0.00 and 1.00.

**agupta** · 05-23-2011, 11:40 AM

I would appreciate if somebody could please help me on this.
Thanks.

**Colin Legg** · 05-23-2011, 12:26 PM

Hi,

See if this works for you:

Please Login or Register  to view this content.

**agupta** · 05-23-2011, 02:07 PM

Thanks for the code Collin. It's the same problem again, the code deleted many of the values but not all. It hope it's not because of the decimal places?

**Colin Legg** · 05-23-2011, 02:09 PM

Hi - the code I posted will delete values which are exactly 0 or 1. It doesn't matter if the 1 is formatted as 1.00.

Please post a sample workbook.

**agupta** · 05-23-2011, 03:13 PM

Thanks Collin. I have attached the workbook. Can you please run the macro on the first sheet (SemiT) and see why is it not deleting all the 0's and 1's?Thanks for your help.

**ChemistB** · 05-23-2011, 03:36 PM

If you click on D8 (for example) you'll see in the formula bar, the actual value is 0.99999988079071.

**agupta** · 05-23-2011, 03:40 PM

Ahhh.....there u go! I should have thought of that! Thanks.

**Colin Legg** · 05-23-2011, 03:40 PM

Hi agupta

It leaves some cells because they are not exactly equal to 0 or 1.

For example, if you look at cell D8, because it is formatted to 2 decimal places it is displayed as 1.00, but if you look in the formula bar you can see that the real value is 0.99999988079071.

**Colin Legg** · 05-23-2011, 03:41 PM

Originally Posted by ChemistB

If you click on D8 (for example) you'll see in the formula bar, the actual value is 0.99999988079071.

LOL... how funny that we both picked the same cell to demonstrate the point.

**agupta** · 05-23-2011, 03:48 PM

My bad. should have figured that out. Anyways, thanks Colin and ChemistB for your help.

**martindwilson** · 05-23-2011, 03:52 PM

try multiplying the whole lot by 100000000000 then divide by 100000000000 that should change

0.99999988079071 to 1

**agupta** · 05-23-2011, 04:16 PM

@Martin: Actually, i want to keep 0.99 as 0.99 without rounding it off to 1.I want macro to only delete cells with exact 0's and 1's. Do you know how can i make excel to NOT round off the values to 1?

**ChemistB** · 05-23-2011, 04:26 PM

It's not actually rounding them. It's just showing them to 2 decimal places. If you format the cells to General, you'll see the whole thing. You'll also need to adjust your cell width.

**agupta** · 05-23-2011, 04:57 PM

@Chemist: I know, when i change the cell width it shows me the entire thing, but can i make it show me only 2 decimal places but without rounding off?

**ChemistB** · 05-24-2011, 09:49 AM

Not without a formula or code. =TRUNC(A1,2) truncates a value rather than rounding it. Sorry.

**agupta** · 05-25-2011, 01:31 PM

Thanks chemist for the solution. And sorry to bug you again and again but this would be my last question. Can you tell me if i can apply this formula on the values in the same cell as the values itself, meaning, can i get the result in the same cell on which i apply the formula without creating a new column to get the results?
Thanks a lot!

**agupta** · 05-25-2011, 03:14 PM

I keep on running into trouble on this. In the attached sheet, i first truncated the values to 2 decimal places and then ran the macro to delete cells with 0's and 1's. It still shows me some 0's and 1's even after running the code! Can you please have a look at it.
Thanks.

**broro183** · 05-25-2011, 07:49 PM

hi Agupta,

I can't see any code in the file you've uploaded. Can you please reattach a file which contains your latest version of the code?

Rob

**agupta** · 05-26-2011, 12:13 PM

Thanks broro183. Sure, please find the file attached containing the code. Thanks.

**Colin Legg** · 05-26-2011, 12:17 PM

Hi,

My suggestion is that you try running the code I posted in #8 instead.

**broro183** · 05-26-2011, 05:39 PM

hi,

Blimey!
Post #8 is waaaaaayyyyy back in the thread. I think it's the way to go

I can't confidently say why the current code is missing some 0's & 1's but I imagine that the act of deleting the cells one at a time during the process changes what excel/vba interprets as the TotalRng. This would be consistent with having a cell linked to another cell using a formulae & then deleting the precedent cell, ie the result of the dependent cell will then show a #REF! error. Colin's code looks good to me because it avoids this issue of deleting cells during the loop. Also, the act of deleting one unioned range should be faster, in this case, than the multiple individual deletions

Rob

**agupta** · 06-01-2011, 11:53 AM

Thankyou very much everyone. Colin's code worked!

**agupta** · 09-21-2011, 04:11 PM

Hi Colin,

I again needed to use your code mentioned in # 8 for removing 0's and 1's, but this time i want to keep the cells blank instead of shifting the cells up. Can you please modify the code for that?

Thankyou very much,
Aman

**Colin Legg** · 09-21-2011, 04:39 PM

Sure, just change this line:

Please Login or Register  to view this content.

To this:

Please Login or Register  to view this content.

Of course, you might want to change the variable name throughout the code to something more appropriate - such as rngToClear.

**agupta** · 09-21-2011, 04:44 PM

Thanks a lot Colin!

**agupta** · 09-22-2011, 12:30 PM

Hi Colin,

I needed a little more modification in the code. Can you please modify the code to remove/delete the entire row for a cell containing 0's and 1's instead of just deleting that particular cell?

Thanks,
Aman

**Colin Legg** · 09-22-2011, 12:43 PM

Sure, this line of code:

Please Login or Register  to view this content.

Becomes:

Please Login or Register  to view this content.

**agupta** · 09-22-2011, 12:51 PM

Awesomeness! Thanks.

**Colin Legg** · 09-22-2011, 12:55 PM

Acutally, not quite. It will error if there are non-contiguous cells containing 1 or 0 on the same row. Amend that single line to this instead:

Please Login or Register  to view this content.

**arthurbr** · 09-22-2011, 01:15 PM

Agupta,
when you have a new question, please start a new thread. If you feel it is necessary, add a link to the original one.

Thread closed

Delete cells with a specific value

LinkBack

Thread Tools

Display

Delete cells with a specific value

Re: Delete cells with a specific value

Re: Delete cells with a specific value

Re: Delete cells with a specific value

Re: Delete cells with a specific value

Re: Delete cells with a specific value

Re: Delete cells with a specific value

Re: Delete cells with a specific value

Re: Delete cells with a specific value

Re: Delete cells with a specific value

Re: Delete cells with a specific value

Re: Delete cells with a specific value

Re: Delete cells with a specific value

Re: Delete cells with a specific value

Re: Delete cells with a specific value

Re: Delete cells with a specific value

Re: Delete cells with a specific value

Re: Delete cells with a specific value

Re: Delete cells with a specific value

Re: Delete cells with a specific value

Re: Delete cells with a specific value

Re: Delete cells with a specific value

Re: Delete cells with a specific value

Re: Delete cells with a specific value

Re: Delete cells with a specific value

Re: Delete cells with a specific value

Re: Delete cells with a specific value

Re: Delete cells with a specific value

Re: Delete cells with a specific value

Re: Delete cells with a specific value

Re: Delete cells with a specific value

Re: Delete cells with a specific value

Re: Delete cells with a specific value

Re: Delete cells with a specific value

Re: Delete cells with a specific value

Re: Delete cells with a specific value

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions