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

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)

"Otto Moehrbach" <(E-Mail Removed)> wrote in message
news:OkenrX$(E-Mail Removed)...
> 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
>
>

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

--
Gary's Student


"Otto Moehrbach" wrote:

> 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
>
>
>

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" <(E-Mail Removed)> wrote in message
news:OkenrX$(E-Mail Removed)...
> 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
>

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
"Otto Moehrbach" <(E-Mail Removed)> wrote in message
news:OkenrX$(E-Mail Removed)...
> 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
>