Excel Macro: Hiding Zero Valudes for All Worksheets

This Excel VBA macro hides zero values for all worksheets in an active workbook.

Macro Examples

Sub HideZeroValues()

  Worksheets.Select
  ActiveWindow.DisplayZeros = False

End Sub

Description

I’ll explain about this macro.

Selecting All Worksheets

Sub...

  Worksheets.Select

The 3rd line selects all worksheets by the Worksheet.Select method.

Worksheets

The Application.Worksheets property returns the Sheets collection object that represents all the worksheets (without chart-sheets) in a workbook.

obj-Excel-Application-Worksheets

The Worksheets property is a member of the Excel.Global class, so we can omit the Application property.

obj-Excel-Worksheets

Worksheets.Select

The Worksheets.Select method selects the Worksheets collection object.

Hiding Zero Values

Sub...

  Worksheets.Select
  ActiveWindow.DisplayZeros = False

The 4th line hides zero valudes by setting the Window.DisplayZeros property to False.

ActiveWindow

The Application.ActiveWindow property returns the Window object that represents an active window.

obj-Excel-Application-ActiveWindow

The ActiveWindow property is also a member of the Excel.Global class, so we can omit the Application property.

obj-Excel-Global-ActiveWindow

ActiveWindow.DisplayZeros

The ActiveWindow.DisplayZeros (Window.Displayzeros property) returns True if the zero value is displayed. In this macro, we hide zero values by setting it to False.

Sub HideZeroValues()

  Worksheets.Select
  ActiveWindow.DisplayZeros = False

End Sub

Properties

http://www.relief.jp/itnote/archives/018451.php

Apply to

  • Excel 2013
  • Excel 2010
  • Excel 2007