How to hide an entire row based on how data is sort

[Wai-Chung]

Hello,

I have a spreadsheet where I have data sorted based on various inputs. However, I would like to know how I can hide rows where there are no data because sometimes, there is a big gap in range from a positive value to a negative value. I know that the negative value is there, but my manager forgets that it is there and assume the spreadsheet is inaccurate.

I am not familiar with VBA, so I hope that someone can show me how to write the code or a formula that would make the rows hidden where there is no data.

As always, thank you in advance to everyone for your help on this forum.

Chung

 

[p45cal]

try his in the Dashboard sheet's code module (right click the sheet's tab and choose View Code)

Private Sub Worksheet_Calculate()
Dim RangeToHide As Range
On Error GoTo myExit
Application.ScreenUpdating = False
Set RangeToCheck = Range("E13:E71").Offset(, Sheets("Sheet3").Range("$E$7").Value)
Application.EnableEvents = False
RangeToCheck.EntireRow.Hidden = False
On Error Resume Next
For Each cll In RangeToCheck.Cells
  If cll.Value = 0 Then
    If RangeToHide Is Nothing Then Set RangeToHide = cll Else Set RangeToHide = Union(RangeToHide, cll)
  End If
Next cll
If Not RangeToHide Is Nothing Then RangeToHide.EntireRow.Hidden = True
myExit:
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub

 

[Wai-Chung]

Thank you for your help p45cal even though my post was at the wrong location. I tried copying and pasting the code but it does not seem to be working. I guess I will just ignore it for now and read through all other forums and hopefully find something similar.

thank you once again.

Chung

 

[p45cal]

See attached and comments in code. It works on Dashboard (not implemented for Dashboard -1).

 

[Wai-Chung]

Thank you for the help p45cal. It works great. I just have to digest the code, but at least I understand a little of what the code is referring to. Now I just have to learn how to write it.

Chung