Hide Cells with Zero values

pambard

New Member
Joined
Dec 21, 2005
Messages
8
Is there any way to hide cells with Zero values? I have around 2000 rows and need to hide Zero values before printing.

Thanks, Pravin
 

Some videos you may like

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.

stemar

Board Regular
Joined
Mar 16, 2002
Messages
248
Try this:

Sub Hide()

Const LastRow As Integer = 2000
Const DataCol as Integer = (the test column)
Dim Row As Integer

For Row = 1 To LastRow
If CInt(Cells(Row, DataCol)) = 0 Then
ActiveSheet.Rows(Row).Hidden = True
Else
ActiveSheet.Rows(Row).Hidden = False
End If
Next Row

End Sub

Put your own values in the constants. (You could just put values in the code. I've got in the habit of using constants because with more complex code, it's a lot easier to figure out what's going on a year later!)

Using CInt means it will still work if there are errors in the column, otherwise it falls over
 

Scott Huish

MrExcel MVP
Joined
Mar 17, 2004
Messages
19,958
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
How about something like this:

Code:
Private Sub Workbook_BeforePrint(Cancel As Boolean)
ActiveWindow.DisplayZeros = False
End Sub

Code:
Sub TurnOnZeros()
ActiveWindow.DisplayZeros = True
End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,119,002
Messages
5,575,497
Members
412,670
Latest member
Khin Zaw Htwe
Top