Problem with Hiding Every Other Column - Still need Help

[samdev]

I posted this earlier and received a couple of solutions, but they did
not work - any ideas - below is a recap:

I have two workbooks/files - both workbooks have the same macro below
that
hides every other column starting with Column B.

Sub hidecol()


Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False
For i = 2 To 255 Step 2
If Columns(i).Hidden = False Then
Columns(i).Hidden = True
Else
Columns(i).Hidden = False
End If
Next i
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
End Sub


In workbook/file 1 it works just fine but in Workbook 2, it stops at
the
following line: Columns(i).Hidden = True

The error msg = "unable to set the Hidden Property of the range
class".


The only difference (that I can detect) between the 2 workbooks is in
Workbook 2 there are more columns that contain data than in workbook
1.


Past suggestions were as follows:

# 1 "Comments and other objects on the screen can stop columns hiding.
If
you have such, right click them, go to format and in properties
choose
"move and size with cells". " I had already checked this setting.

# 2

Sub hidecol()
Dim ws As Worksheet
Dim i As Long
Set ws = Worksheets("Sheet1")
Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False
ws.Columns("A:IV").Hidden = False
For i = 2 To 255 Step 2
ws.Columns(i).Hidden = True
Next i
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
End Sub


I tried this code but get the same error msg as above (my code).

Any other ideas - much thx,

 

[Gord Dibben]

If the worksheet is protected your macro will crash and give you the exact
error message you are seeing.


Gord Dibben MS Excel MVP

 

[ct60]

Hi samedev -

I seem to recall running into this some time ago but unfortunately I can not
seem to locate how I got around it.

Does the worksheet which doesn't work have frozen panes or comments on the
frozen part of the frozen pane? I seem to recall that that can be a problem.
You should probably unfreeze the pane first in any case and then hide the
columns.

Also, can you see what is the value of variable "i" when you egt your error
message? You can see that by hovering the mouse over the variable. If this
dfails you can add a Debug.print statement and then use Ctr-G to see where it
stopped. It would be good to know if the problem ocurs right at the start or
somewhere toward the end.

Please post more and I will try to help.

Best Regards,

Chris

 

[samdev]

Hi samedev -

I seem to recall running into this some time ago but unfortunately I can not
seem to locate how I got around it.

Does the worksheet which doesn't work have frozen panes or comments on the
frozen part of the frozen pane?  I seem to recall that that can be a problem.
 You should probably unfreeze the pane first in any case and then hide the
columns.

Also, can you see what is the value of variable "i" when you egt your error
message?  You can see that by hovering the mouse over the variable.  If this
dfails you can add a Debug.print statement and then use Ctr-G to see where it
stopped.  It would be good to know if the problem ocurs right at the start or
somewhere toward the end.

Please post more and I will try to help.

Best Regards,

Chris

















- Show quoted text -

The two workbooks are the same except the one that I'm having an issue
with has more columns with more data. I have removed comments from the
workbook that is not working and that has made no difference. None of
sheets or workbook, itself, are protected.....

The last column it hides is DH wheras in the workbook that the macro
functions perfectly, all columns are hidden starting at column B.

Any other ideas??

Thx,