Hidden Rows in Excel 1

[simoncpage]

hi all, Does anyone know how to count the number of hidden columns in a range?

Simon

 

[StewartJ]

Loop through the columns and check :

dim colThisColumn as Range
dim intCounter as Integer

intCounter = 0

For Each colThisColumn in Worksheets(1).Columns

If colThisColumn.Hidden = True Then
intCounter = intCounter + 1
EndIf

Next

I haven't tested this but it should be on the right lines.

It would be more efficient if you just worked on UsedRange.

 

[xlbo]

summat like this should work - depends on how the rangte is specified tho:

mRange = "A1:N50"
numhidden = 0
numcols = Asc(Mid(mRange, 4, 1)) - Asc(Left(mRange, 1)) + 1
For i = 1 To numcols
If ActiveSheet.Columns(i).Hidden = True Then
numhidden = numhidden + 1
Else
End If
Next i Rgds
~Geoff~

 

[ladybyrd]

Hopefully, this will get you started. It yields the correct result, but doesn't automatically recalculate, even with the Application.Volatile statement. I tried using Application.Calculate in the SheetChange event, but hiding and unhiding columns did not make that event fire. I'm sure there's a way to force recalculation when you need it, though.
Application.Volatile True
If rg.Rows.count > 1 Then
Set rg = rg.Resize(1)
End If
On Error Resume Next

For i = 1 To rg.Columns.count
If Columns(i).Hidden Then
n = n + 1
End If
Next
ColNotVisible = n

HTH