Auto Hide Empty Rows

Glasgowsmile

Board Regular
Joined
Apr 14, 2018
Messages
165
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?

VBA Code:
    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
 

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
57,510
Office Version
  1. 365
Platform
  1. Windows
How about just using Filters to hide the blanks?
 

Glasgowsmile

Board Regular
Joined
Apr 14, 2018
Messages
165
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

MrExcel MVP
Joined
May 26, 2009
Messages
17,442
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
Does this do what you want (change range to suit)?
VBA Code:
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
 
Solution

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
57,510
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

Glasgowsmile

Board Regular
Joined
Apr 14, 2018
Messages
165
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)?
VBA Code:
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

MrExcel MVP
Joined
May 26, 2009
Messages
17,442
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
You are welcome - thanks for the reply.
 

Watch MrExcel Video

Forum statistics

Threads
1,132,708
Messages
5,654,856
Members
418,155
Latest member
demasisi

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top