Counting visible cells

[Otto Moehrbach]

Excel XP, Win XP
Helping an OP.
I have a range with numerous hidden columns.
The hidden columns are not contiguous.
Columns A:B are always visible.
I want to count the number of visible columns.
Row 4 is the header row, so I want to count the number of visible cells in
Row 4.
I use the following code to give me that count. I get an error on this
line.
The error message is "Unable to get the SpecialCells property of the range
class."
ColCount = Range("A4", Cells(4, Columns.Count).End(xlToLeft)) _
.SpecialCells(xlCellTypeVisible).Count
This seems simple to do. What am I doing wrong? Thanks for your time.
Otto

 

[Bob Phillips]

I can't reproduce it Otto.

I protected the sheet, tried a row with no data , a row with no data and no
hidden columns, nothing failed.

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

 

[Gary''s Student]

Sub sistance()
Dim c As Integer
For c = 1 To 256
If Columns(c).EntireColumn.Hidden = True Then
colcount = colcount + 1
End If
Next
MsgBox (colcount)
End Sub

 

[Otto Moehrbach]

Some more information.
This line of code is in a Workbook_BeforePrint macro.
There is only one sheet in the file.
I put that line of code, as follows, in a regular module in the same file
and it runs fine.
MsgBox Range("A4", Cells(4, Columns.Count).End(xlToLeft)) _
.SpecialCells(xlCellTypeVisible).Count
Then I took ALL the code from the Workbook module and put it into a regular
macro (with a different macro name) and it worked just fine.
The problem apparantly is because I have that code in a Workbook event
macro.
So there is something here that I have to learn. What happened? Thanks for
your time. Otto

 

[Otto Moehrbach]

All is well.
I changed the Workbook_BeforePrint macro code to:
Cancel = True
Call ThePrintMacro
'All the code went into the ThePrintMacro
Apparently there are some rules about what you can and can't put into a
Workbook_BeforePrint macro.
Does anybody know what the problem was? Thanks for your time. Otto