Conditional Format to flag if anything is Hidden?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Is there a way to get conditional formatting to flag me if I have any hidden columns or rows in a worksheet? I know, I know, I know it would be easy to simply check. But I keep forgetting. Thanks

Todd
 
Todd,

No. Hiding rows and columns doesn't change anything about the cell value
that can be detected through a formula - SUBTOTAL, which only acts on
visible cells, requires that those cells not included be hidden through
filtering, not through row height modification.

You could use a User-Defined-Functions that would return TRUE if any row or
column was hidden:

Function HiddenRowOrCol() As Boolean
Dim myRow As Long
Dim myRowCount As Long
Dim myCol As Integer
Dim myColCount As Integer
Dim mySht As Worksheet
Set mySht = Application.Caller.Parent

myColCount = mySht.UsedRange(mySht.UsedRange.Cells.Count).Column
For myCol = 1 To myColCount
If mySht.Columns(myCol).Hidden Then
HiddenRowOrCol = True
Exit Function
End If
Next myCol

myRowCount = mySht.UsedRange(mySht.UsedRange.Cells.Count).Row
For myRow = 1 To myRowCount
If mySht.Rows(myRow).Hidden Then
HiddenRowOrCol = True
Exit Function
End If
Next myRow

End Function

Used like

=HiddenRowOrCol()

This is not volatile, so would require recalculation to accurately reflect
any changes.

HTH,
Bernie
MS Excel MVP

Todd said:
Is there a way to get conditional formatting to flag me if I have any
Click to expand...
hidden columns or rows in a worksheet? I know, I know, I know it would be
easy to simply check. But I keep forgetting. Thanks,
 
If there is a specific column, or only a few columns, you
can use the CELL function elsewhere in the spreadsheet,
where you will see it.

=CELL("width",G1) for example will return a zero if the
width of cell G1 is zero (hidden). You'll have to force
a calculation with the F9 key or make an entry to get it
to work since changing cell width doesn't trigger
automatic recalculation.

-----Original Message-----
Is there a way to get conditional formatting to flag me
Click to expand...
if I have any hidden columns or rows in a worksheet? I
know, I know, I know it would be easy to simply check.
But I keep forgetting. Thanks,
 
Bernie Deitrick said:
Todd,

No. Hiding rows and columns doesn't change anything about the cell value
that can be detected through a formula - SUBTOTAL, which only acts on
visible cells, requires that those cells not included be hidden through
filtering, not through row height modification.

You could use a User-Defined-Functions that would return TRUE if any row or
column was hidden:

Function HiddenRowOrCol() As Boolean
Dim myRow As Long
Dim myRowCount As Long
Dim myCol As Integer
Dim myColCount As Integer
Dim mySht As Worksheet
Set mySht = Application.Caller.Parent

myColCount = mySht.UsedRange(mySht.UsedRange.Cells.Count).Column
For myCol = 1 To myColCount
If mySht.Columns(myCol).Hidden Then
HiddenRowOrCol = True
Exit Function
End If
Next myCol

myRowCount = mySht.UsedRange(mySht.UsedRange.Cells.Count).Row
For myRow = 1 To myRowCount
If mySht.Rows(myRow).Hidden Then
HiddenRowOrCol = True
Exit Function
End If
Next myRow

End Function

Used like

=HiddenRowOrCol()

This is not volatile, so would require recalculation to accurately reflect
any changes.

HTH,
Bernie
MS Excel MVP


hidden columns or rows in a worksheet? I know, I know, I know it would be
easy to simply check. But I keep forgetting. Thanks,
Click to expand...
 
Bernie,

I was excited to see your user defined function for finding a hidden row. However, I need to take this function a step further if possible. Can this function be refined to find only hidden rows that have data in them?
 
Back
Top