I have a number of cells in a range (lets say A14:J52).
Some of these cells are "paste linked" from cells on other sheets.

Unfortunately, when the original cell is empty, the value of the
"paste linked" cell shows as "0".
(not just blank like the original)

Is there a way to keep the "paste linked" cell showing "0"?

If not, how would I go about using VBA to systematically go through
the above described range and delete those zeros?

At the point I ran this code, any "0" could be deleted from the range
safely whether it was a "paste linked" cell or not.


Any help would be appreciated.


Doug

format the cell as #


On Nov 16, 11:49*pm, Doug Howell wrote:
I have a number of cells in a range (lets say A14:J52).
Some of these cells are "paste linked" from cells on other sheets.

Unfortunately, when the original cell is empty, the value of the
"paste linked" cell shows as "0".
(not just blank like the original)

Is there a way to keep the "paste linked" cell showing "0"?

If not, how would I go about using VBA to systematically go through
the above described range and delete those zeros?

At the point I ran this code, any "0" could be deleted from the range
safely whether it was a "paste linked" cell or not.

Any help would be appreciated.

Doug

Changing the cell to number doesn't work.
It still shows "0" in the linked cell.

Format as Custom

Type the # sign only


Gord Dibben MS Excel MVP

On Mon, 16 Nov 2009 12:37:22 -0800 (PST), Doug Howell
wrote:

Changing the cell to number doesn't work.
It still shows "0" in the linked cell.

ToolsOptionsViewZero Values.

That affects any zero, though - even the legitimate ones.
Can also use Conditional formatting - font & Cell the same colour
(white?).


On 16 Nov, 20:42, Gord Dibben <gorddibbATshawDOTca wrote:
Format as Custom

Type the # sign only

Gord Dibben *MS Excel MVP

On Mon, 16 Nov 2009 12:37:22 -0800 (PST), Doug Howell



wrote:
Changing the cell to number doesn't work.
It still shows "0" in the linked cell.

Too fast with the send button.

The custom format of # will hide the zeros but not show decimals if your
blank source cell becomes a number like 123.45

Rather than pasting links it is better, not easier, to use a formula like

=IF(Sheet1!A1="","",Sheet1!A1)


Gord

On Mon, 16 Nov 2009 12:42:53 -0800, Gord Dibben <gorddibbATshawDOTca wrote:

Format as Custom

Type the # sign only


Gord Dibben MS Excel MVP

On Mon, 16 Nov 2009 12:37:22 -0800 (PST), Doug Howell
wrote:

Changing the cell to number doesn't work.
It still shows "0" in the linked cell.

For Excel 2007, this is actually easier....

Excel Options - Advanced - Display Options for this Worksheet
uncheck the "show a zero in cells that have zero value" box