Need help with ClearContents

[MPFraser7]

Hello,

I am trying to show blank cells instead of 0 for the whole worksheet.

Here is what I've come up with:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
For Each cell In SomeRange
If (cell.Value = 0) Then cell.ClearContents
Next
End Sub

How do I get this to work?

Thanks!

 

[BQardi]

try:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
For Each cell In UsedRange
If (cell.Value = 0) Then cell.ClearContents
Next
End Sub
NOTE that this macro will run EVERY time you select a new cell. If you only need this to run once, delete the macro afterwards or put it in a module and assign it to a button...

 

[My Aswer Is This]

Do you really need to do this every time you move to a different cell on your sheet?

That's what selection change is all about.

 

[Rick Rothstein]

You can do this without using code. Here is the procedure for XL2010 (it will be similar, but maybe not exact, in other versions. Bring up the "Excel Options" dialog (File tab, Options item), select "Advanced" in the left-hand list, scroll down to where it says "Display options for this worksheet" and uncheck the Checkbox labeled "Show a zero in cells that have zero value", click "OK" to finish.

 

[Joe4]

No need for VBA at all. There is a simple property you can use to show all zeroes as blanks.
See: https://support.office.com/en-us/ar...o-values-3ec7a433-46b8-4516-8085-a00e9e476b03