How to Calculate Number of Hidden Columns in Workshee

[dallin01]

Is there a function or vb code that will give me the number of hidden columns in a particular worksheet? Thanks

 

[Gary McMaster]

Try this:

Public Sub Test()

Dim iCount As Integer
Dim iHidden As Integer

For iCount = 1 To ActiveSheet.Columns.Count
    If ActiveSheet.Columns(iCount).Hidden = True Then iHidden = iHidden + 1
Next iCount

MsgBox "There are " & iHidden & " hidden columns in the active worksheet"

End Sub

 

[dallin01]

Perfect. thanks!

 

[HaHoBe]

Hi,

why not use Constants instead of looping?

Sub bla()
On Error Resume Next
With ActiveSheet.Rows(1)
  MsgBox "There are " & .Cells.Count - .SpecialCells(xlCellTypeVisible).Cells.Count & " hidden columns in the active worksheet"
End With
End Sub

Ciao,
Holger