Modified Macro Hide Blank

[JERRY89]

Dear All,

I do have a report where i cant use hide row if certain column is blank so I need to ask is there any possibility to use the macro below to hide a report if the entire row is blank.

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

I have attached the file as below.

https://www.dropbox.com/s/2o5681z132r4e ... .xlsx?dl=0" onclick="window.open(this.href);return false;

[HansV]

Since your data are in columns A to J, try this:

Code: Select all

Sub HideBlanks()
    Dim r As Long
    Dim m As Long
    Application.ScreenUpdating = False
    m = Cells.Find(What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    For r = 2 To m
        Range("A" & r).EntireRow.Hidden = _
            (Application.CountBlank(Range("A" & r).Resize(1, 10)) = 10)
    Next r
    Application.ScreenUpdating = True
End Sub

[JERRY89]

Hi Hans,

Thanks A Lot it work perfectly..

[JERRY89]

Hi Hans,

May i get your advice, because i try to put some simple formula to lookup data =Sheet2!C3 in column I sheet1 and i go to Option-Advance-Change the option to not show Zero so although the result is shown blank but this Macro unable to hide the row that contain formula. Is there any alternative in this situation.

[Rudi]

Try this

If column I has a formula, and guarenteed to not be blank...use:

Code: Select all

Sub HideBlanks()
    Dim r As Long
    Dim m As Long
    Application.ScreenUpdating = False
    m = Cells.Find(What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    For r = 2 To m
        Range("A" & r).EntireRow.Hidden = _
            (Application.CountBlank(Range("A" & r).Resize(1, 10)) = 9)
    Next r
    Application.ScreenUpdating = True
End Sub

[JERRY89]

Hi Rudi,

Thanks for the guidance..It really solved my problem.