Automatically HIDING and UNHIDING ZEROS

[dahowarduk]

Can anyone show me how to :-
Set up an automated facility in Excel to NOT show a zero in cells that have a zero value, or later to then show a zero in cells.
i.e. the same reusable default ‘shortcut’ (for any spreadsheet) as an alternative to
FILE ~ OPTIONS ~ ADVANCED ~ but (DON’T) SHOW A ZERO IN CELLS THAT HAVE A ZERO VALUE

 

[alansidman]

Home-->Editing-->Find & Select-->Replace
Replace What=0
Replace With= ""

 

[dahowarduk]

Thanks. But I want a macro or 'button'

 

[p45cal]

Sub ToggleZeros()
ActiveWindow.DisplayZeros = Not ActiveWindow.DisplayZeros
End Sub

You could put it in personal.xlsb and create a link in the Quick Access Toolbar which will work as a toggle button in any workbook but on your computer only
or
put it into a standard code module (or the workbook code module) and create a button to run it, which will run only in that workbook.



Optionally use point 6 (Modify…) to change the symbol that appears in the QAT.

 

[dahowarduk]

Thanks, but I'd need an idiot proof dummy's guide to step me through. I'm far too old to try without such a guide

 

[alansidman]

Standard Module
How to install your new code
Copy the Excel VBA code
Select the workbook in which you want to store the Excel VBA code
Press Alt+F11 to open the Visual Basic Editor
Choose Insert > Module
Edit > Paste the macro into the module that appeared
Close the VBEditor
Save your workbook (Excel 2007+ select a macro-enabled file format, like *.xlsm)

 

[dahowarduk]

thanks

 

[p45cal]

Can I take it you're all sorted now?

 

[dahowarduk]

Sorry, I would have been out of y comfort zone, so I gave up. Instead I created a new default template that includes zeros hidden