Auto Hide Empty Rows

[Glasgowsmile]

Hello,

I'm trying to set up some VBA that will auto-hide cells with no values. They have formulas in them but if they return no value then I'd rather hide them.

Currently I've got this which instead of using blank cells (because I couldn't get it to work), I used a helper column with True / False. While this does work it's slow and takes a bit of time to process due to the amount of rows. How can I either improve this code below or what code would be better to achieve the same thing?

    Application.ScreenUpdating = False
   For Each Col In Range("AU5:AU700")
        If Col.Value = "False" Then
            Col.EntireRow.Hidden = True
        Else
            Col.EntireRow.Hidden = False
        End If
    Next Col
    Application.ScreenUpdating = True

 

[Joe4]

How about just using Filters to hide the blanks?

 

[Glasgowsmile]

How about just using Filters to hide the blanks?

Certainly an option, yes, but it'd have to be done for multiple columns and not all the users of the document are comfortable enough with Excel to know how to do that.

Alternatively though, if you know how I could remove "blanks" as an option each time the data was updated without having to manually do it - that would also work.

 

[JoeMo]

Does this do what you want (change range to suit)?

Sub HideRowIf()
Dim R As Range, F As Range, c As Range
Set R = Range("A1:A20")  'change range to suit
With Application
    .Calculation = xlCalculationManual
    .ScreenUpdating = False
End With
On Error Resume Next
Set F = R.SpecialCells(xlCellTypeFormulas)
If Not F Is Nothing Then
    For Each c In F
        If c.Value = "" Then
            c.EntireRow.Hidden = True
        Else
            c.EntireRow.Hidden = False
        End If
    Next c
Else
    MsgBox "no formulas in the range " & R.Address(0, 0)
End If
With Application
    .Calculation = xlCalculationAutomatic
    .ScreenUpdating = True
End With
End Sub

 

[Joe4]

Filters can also be applied via VBA, so the users don't need to do them manually!

 

[Glasgowsmile]

Filters can also be applied via VBA, so the users don't need to do them manually!

Yeah, I just don't know that VBA and people will need to sort if in many different ways at times.

Does this do what you want (change range to suit)?

Sub HideRowIf()
Dim R As Range, F As Range, c As Range
Set R = Range("A1:A20")  'change range to suit
With Application
    .Calculation = xlCalculationManual
    .ScreenUpdating = False
End With
On Error Resume Next
Set F = R.SpecialCells(xlCellTypeFormulas)
If Not F Is Nothing Then
    For Each c In F
        If c.Value = "" Then
            c.EntireRow.Hidden = True
        Else
            c.EntireRow.Hidden = False
        End If
    Next c
Else
    MsgBox "no formulas in the range " & R.Address(0, 0)
End If
With Application
    .Calculation = xlCalculationAutomatic
    .ScreenUpdating = True
End With
End Sub

This worked flawlessly, thanks so much!

 

[JoeMo]

You are welcome - thanks for the reply.