Cell Which Tells You If Coulmns Are Hidden

[burdie87]

Hi,
I am looking for a solution to the following problem.

I need a cell that will say if coulmns in the worksheet are hidden or not but im not sure about how to do it, if it is at all possible.

Alan.

 

[Andrew Poulsom]

You could try this UDF:

Function ColsHidden() As Boolean
    Dim c As Integer
    Application.Volatile True
    With Application.Caller.Parent
        For c = 1 To .Columns.Count
            If .Columns(c).EntireColumn.Hidden = True Then
                ColsHidden = True
                Exit Function
            End If
        Next c
    End With
End Function

Put it in a cell like this:

=ColsHidden()

Note that it won't update automatically when you hide/unhide columns. But it will update if the worksheet is recalculated or you press F9

 

[burdie87]

Hey,
I am quite simple but where do i put the above code to make it woprk cause all i get is name#.
Alan

 

[Andrew Poulsom]

In your workbook press Alt+F11 to go to the Visual Basic Editor. Click your workbook in the Project window and choose Insert|Module from the menu. Paste my code into the window on the right. Press Alt+F11 again to return to your workbook.

 

[Legacy 1363]

Here's another way which is not exactly what you requested but which might be useful since it will update automatically when columns are hidden/unhidden.

Select row 1 and put the following formula in ConditionalFormatting :-

=CELL("Width",B1)=0

Your format of choice will appear in any cell in row 1 immediately preceding a hidden column (except, of course, in the case of column A being hidden).