|
|
|
|
|
|
|
|
|
Excel
|
Hide The Zeros
|
|
|
Ease of Use
|
Easy
|
|
Version tested with
|
2000
|
|
Submitted by:
|
johnske
|
|
Description:
|
Worksheets will no longer show zero values.
|
|
Discussion:
|
When worksheets contain lots of formulas and/or links to other sheets or workbooks with many zero values it can be very difficult to read through this to find the data you want. Display zero values is one of many other options available in the 'Tools' menu. However, when using this option, it only applies your choice to the active sheet. So if you want to use the option of not showing zero values to the whole workbook it can take some time to apply it manually. In such cases, the following code can save you a lot of time.
|
|
Code:
|
instructions for use
|
Option Explicit
Private Sub Workbook_SheetActivate(ByVal Sh As Object)
ActiveWindow.DisplayZeros = False
End Sub
|
|
How to use:
|
- Open an Excel workbook
- Select Tools/Macro/Visual Basic Editor
- In the VBE window, select Tools/Project Explorer
- Select the ThisWorkbook module
- Copy and paste the code into the Module
- Now select File/Close and Return To Microsoft Excel
- Dont forget to save your changes...
|
|
Test the code:
|
- Type in a zero (0) in any cell on any sheet and note that this 0 is not displayed in the cell.
- The attachment contains two almost identical workbooks:
- DontKillZeros does NOT have the above code in it, and you'll notice all zero values are shown
- KillZeros DOES have the above code in it, and you'll notice no zero values are shown.
|
|
Sample File:
|
KillZeros.zip 8.08KB
|
|
Approved by mdmackillop
|
|
This entry has been viewed 234 times.
|
|
|