Hide Blanks if Entire Row is Blank

[JERRY89]

Hi All,

I do have a macro as below to hide rows in my accounting report as my report are having four important column that is Column B= Opening,C=Purchase ,D=Payment and E=Closing so my problem is how can i set a macro Range rather that using B1 till B574 to a more analytical method which is if the whole column value is =Zero then entire row can be hide.


Sub HideBlanks()
For Each Cell In Range("B1:B574")
If Cell.Value = "0" Then
Cell.EntireRow.Hidden = True
Else
Cell.EntireRow.Hidden = False
End If
Next Cell
End Sub

[HansV]

Should a row be hidden if the values in columns B to E are really blank, or if the values are 0?

[JERRY89]

Hi Mr Hans,

I want the row to be hidden if entire row value is equal with 0 from B to E

[HansV]

Here is a modified version of the macro:

Code: Select all

Sub HideBlanks()
    Dim Cell As Range
    Application.ScreenUpdating = False
    For Each Cell In Range("B1:B574")
        Cell.EntireRow.Hidden = (Application.CountIf(Cell.Resize(1, 4), 0) = 4)
    Next Cell
    Application.ScreenUpdating = True
End Sub

[JERRY89]

Hi Hans,
I have tried to install the above Macro but it does not work or is it because of i am using Microsoft Office 2010???

[HansV]

Do you get an error message? If so:
- What is the text of the error message?
- If you click Debug when the error occurs, which line is highlighted?

[JERRY89]

The problem is there is no any error message appear ?? Can u help me to see my workbook as below??

https://www.dropbox.com/s/9jzzd0r9zbf7i ... .xlsm?dl=0" onclick="window.open(this.href);return false;

[HansV]

Higher up in this thread, I asked you "Should a row be hidden if the values in columns B to E are really blank, or if the values are 0?"
To which you replied "I want the row to be hidden if entire row value is equal with 0 from B to E".
There are no rows in your worksheet in which columns B to E contain a 0, so I suspect you answered my question incorrectly: you want to hide the rows in which columns B to E are blank, not 0.
To do that, change the macro to

Code: Select all

Sub HideBlanks()
    Dim Cell As Range
    Application.ScreenUpdating = False
    For Each Cell In Range("B1:B574")
        Cell.EntireRow.Hidden = (Application.CountBlank(Cell.Resize(1, 4)) = 4)
    Next Cell
    Application.ScreenUpdating = True
End Sub

[JERRY89]

Hi Mr Hans,

Thanks alot ,i am sorry for the incorrect information i had given to you.All my problem had be solved ,once again thanks you so much for your expertise guidance