Detect Hidden Columns

[Guest]

How can I detect if any columns have been hidden on a worksheet in vba?

I would like to use this to prevent a modal form (called from wsheet1)
initialising and also in a Workbook_BeforeClose event which contains 4
wsheets.

Geoff

 

[Guest]

Sub geoff()
For i = 1 To 256
If Columns(i).EntireColumn.Hidden = True Then
MsgBox ("column " & i & " is hidden")
End If
Next
End Sub

 

[Guest]

Thank you, I can adapt from that.

Geoff

 

[NickHK]

Given that people are using v2007 now, it would be better not to hard code
the row count:

Private Sub CommandButton1_Click()
Dim i As Long

With ActiveSheet.Columns
For i = 1 To .Count
If .Item(i).EntireColumn.Hidden = True Then
MsgBox "column " & i & " is hidden"
End If
Next
End With

End Sub

NickHK

 

[Guest]

good point.


b.t.w - what's an item ?

 

[NickHK]

Collections have a member of .Item.

..Columns(i) is equivalent to .Columns.Item(i)

Only so I could include both in the With block.

NickHK

 

[Guest]

thanks