Creat a Macro to hide Zero Value cells & Repeat same macro on all sheets

[Haroon Rashid]

Hello

I am using a huge excel sheet and want to create a macro which can check each cell in a row and if its below 1, hides it and in future if number changes, un-hides it.

Just to explain briefly:

I want Macro to hide the row "Z" only since all the values in the column are Zero, but next month if value changes, I want macro to un-hide.



Please also help me, to use the same macro, on rest of the sheets in file.

I used below macro but it check only Column 2 and hides the row even colum 3 has a value.

"
Sub HideRowsWithZero()
BeginRow = 1
EndRow = 500
ChkCol = 2

For RowCnt = BeginRow To EndRow
If Cells(RowCnt, ChkCol).Value < 1 Then
Cells(RowCnt, ChkCol).EntireRow.Hidden = True
Else
Cells(RowCnt, ChkCol).EntireRow.Hidden = False
End If
Next RowCnt
End Sub
"

Your help is really appreciate.
Regards
Haroon
__________________________________________________________________

Mod edit : thread moved to appropriate forum !​

 

[Hui]

Haroon

Firstly, Welcome to the Chandoo.org Forums

Why do you need a macro?

You can use a Custom Number format to achieve the same

Try the following technique

Select Worksheet1
Hold the Shift Key and Click on the last Worksheet
Press Ctrl+A
Press Ctrl+1
Select the Number Tab, Custom
Enter a Custom number format of say 0,000;-0,000;;@
Press Ok
Click on one of the worksheets to ungroup them

 

[Haroon Rashid]

Thank you Hui,
But that's not what I am looking for. Sorry may be I didn't explained it better.

I am not looking to hide the "Zero", I am looking to hide the rows with Zero.
I will to explain again by below.

If you see Illustration 1, Row "Y" has Zero in May and June so I want to hide it and when we move to next period as shown in Illustration 2, Row Y has number in July, and Row W has all zeros, so I want to unhide the Row Y and hide row W.

 

[Hui]

Thats clearer

Can you upload a sample file?

Is it just the May & June Columns or are there other columns?

Is it just one worksheet or all worksheets

 

[Monty]

Hello Haroon.

Welcome to Chandoo forum.

Please confirm below question.
1) You want hide if entire row is zero / or any one cell contains zero.
2) You meen only for one sheet for entire workbook.

Just for your information we cannot hide one cell...We can hide row or column..

Please help us with the question asked for to help you with the code..

Have a great learning.