Turn zero-valued cells into blank cells while preserving link

Hello everyone,

this is my first post as a forum member, but I've been using the forum for a while and you've already solved a couple of issues for me. So first of all I wanted to THANK EVERYONE for what you've already done for me!

--- SITUATION:

1.) Data in Sheet1 is linked to Sheet2.

--- PROBLEM:

2.) In Sheet1 where cells are blank, they appear as 0s.

--- WHAT I'M LOOKING FOR:

3) Cells that appear as 0's need to be blank/empty (not only "look" blank, so no conditional formatting changing colors!) AND the links need to be preserved.

--- WHAT I'VE TRIED SO FAR

I've tried this, which turns zero-valued cells into blank cells BUT BREAKS the links.

For Each cell In ActiveSheet.UsedRange
If Not IsEmpty(cell) Then
If cell.Value = 0 Then
cell.Value = ""
End If
End If
Next


That doesn't work for me cause the links need to be preserved. Any suggestions????

Thank you so much for your help!!! Talk soon.

Hello,

Could you please explain why you are performing this action using VBA rather than (for instance) using an IF statement in a spreadsheet formula to test for ISBLANK in which case you could return a double quote ("") to add a blank entry to the cell. As this would be within a formula then the link would still be maintained.

Kind regards,
Simon.

Great - glad it worked. Don't forget to add the Linkedcell back in for the FALSE condition of the IF statement :

=IF(ISBLANK(linkedcell);"";linkedcell)

The reason this issue occurs is that if a formula points to a blank cell, then Excel will evaluate it to zero. However, the underlying linked cell remains blank (or empty) and so you can test specifically for this.

Simon

Thanks for the explanation, Simon!!

You're right, I soon realized that I had to add the linkedcell back for the FALSE condition, so I edited the post! This way everyone can see the formula and use it as needed.

Regards,

Andrés