Delete Zero's

[Jenawade]

After I run a macro that changes the line number in a whole bunch of formula's, pastes values and does a save as, I'm left with some zero's in the cells that didn't pull in data. Is there a way to remove just those zero's, but not zero's that are a part of a text/number string, a number, or one of the formula's left in the sheet? For example (below), I don't want to delete any existing zero's in an actual cost in col N, or in a part # in col K, or the # in col G. Is there something I can add to my macro to remove just the zero's if the cell contains nothing other than a single zero?

Excel Workbook
	C	D	E	F	G	H	I	J	K	L	M	N
49	0	0	0	15	0	0.00%	Alligator	1	211168-26S	Nut	1	$0.00000
50	0	0	0	1	0.6754	0.00%	0	1	211168-23S	L50	1	$1.41000
51	0	0	0	100.00%	0.2192	0.00%	0	0	211168-18S	Cap	1	$0.45750
52	0	0	0	100.00%	0.1054	0.00%	0	0	211168-19S	Screw	1	$0.22000
53	0	0	0	100.00%	0	0.00%	0	0	0	0	0	$0.00000
54	0	0	0	100.00%	0	0.00%	0	0	0	0	0	$0.00000
55	0	0	0	100.00%	0	0.00%	0	0	0	0	0	$0.00000
56	0	0	0	100.00%	0	0.00%	0	0	0	0	0	$0.00000
57	0	0	0	100.00%	0	0.00%	0	0	0	0	0	$0.00000
58	0	0	0	100.00%	0	0.00%	0	0	0	0	0	$0.00000
PLQ Form

 

[anandvarma]

use find Control+F and then in click options menu find 0, use replace all

 

[Ruddles]

I think the usual method of achieving this is to use conditional formatting to set the foreground colour of any cells whose value is equal to zero to be the same as the background colour.

If you absolutely must get rid of those poor, harmless little zeroes, as the final step in your macro you could whizz through all the cells in the used portion of the worksheet and clear them. Something like this:-

dim ocell as range
for each ocell in activesheet.usedrange
    if ocell.value=0 then ocell.clearcontents
next ocell

You might need to change activesheet to whatever sheet you're actually operating on.

 

[Jenawade]

Hehe, those poor little buggers are just misunderstood. I just found (and tested) this before I read your reply.

.Rows("4:59").Replace What:="0", Replacement:="", LookAt:=xlWhole

Thanks!

 

[Ruddles]

That'll be much quicker than my VBA.