Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problem with Hiding Every Other Column - Still need Help
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, |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problem with Hiding Every Other Column - Still need Help
If the worksheet is protected your macro will crash and give you the exact
error message you are seeing. Gord Dibben MS Excel MVP On Wed, 15 Oct 2008 07:06:22 -0700 (PDT), samdev wrote: 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, |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problem with Hiding Every Other Column - Still need Help
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" wrote: 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, |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problem with Hiding Every Other Column - Still need Help
On Oct 15, 12:14*pm, ct60 wrote:
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" wrote: 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,- Hide quoted text - - 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, |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Hiding a column of hyperlinks behind a column of numbers | New Users to Excel | |||
Problem with Hiding Every Other Column | Excel Programming | |||
Hiding column if cell in previous column is empty-revised | Excel Programming | |||
Hiding Column Also hiding text | Excel Programming | |||
Need help hiding/unhiding column based on autofilter selection in a different column | Excel Programming |