Run-time error '1004' - Macro for Hiding/Unhiding Columns

[CameronC]

I am trying to create macros to toggle between different views for an excel worksheet but keep getting "Run-time error '1004": Unable to set the Hidden property of the Range class. My goal is to use the same set of code but change the ranges for the hidden columns and unhidden columns. Below is the basic structure I am trying to use.

Sub Hidden_Column_View()

'Unprotect Worksheet
ActiveSheet.Unprotect

'Unhide Columns
Columns("A:HY").EntireColumn.Hidden = False

Range("A:J,M:O,Q:AL,AX:DB,EB:EG,FC:FF,FS:GH,GP:GU,HH:HY").EntireColumn.Hidden = Not Range("K:L,AM:AW,DC:EA,EH:FB,FG:FR,GI:GO,GV:HG,HZ:ID").EntireColumn.Hidden


'Protect Worksheet
ActiveSheet.Protect , AllowFiltering:=True


End Sub

I have tried many different iterations of the formula with no luck. Any assistance would be great! Thanks.

 

[AOB]

Well, the first thing you do is unhide columns A:HY, then you run a check on a non-contiguous range containing those columns to see if it is hidden or not.

Columns A:HY are definitely visible (you've just made them visible) whereas columns HZ:ID may or may not be. Therefore the .EntireColumn.Hidden property would be ambiguous for that range, if such a property existed (it could be True for some columns and False for others, hence it could hold no single value of True or False)

So the logic is flawed.

Regardless of the above, even if HZ:ID were visible, you can't use that property on a range (contiguous or otherwise) - only on individual columns. (Think of it this way; what if your range had a specific number of rows rather than entire columns? You can't hide a column for some rows and not for others - hence that property is not allowed for ranges)

So I would define ranges and then loop through the individual columns within them, and hide / show them that way :

Dim rng As Range
Dim col As Variant

Set rng = ActiveSheet.Range("A:J,M:O,Q:AL,AX:DB,EB:EG,FC:FF,FS:GH,GP:GU,HH:HY")

For Each col In rng.Columns

    col.Hidden = True   ' Or False; depending on your criteria - you'll need to rethink this part...

Next col

 

[steve the fish]

Not an expert by any means but this should toggle. How are you going to implement it though? Cant use a button as it will keep disappearing!

Sub Hidden_Column_View()
ActiveSheet.Unprotect
If Range("A:A").EntireColumn.Hidden = True Then
    Range("A:J,M:O,Q:AL,AX:DB,EB:EG,FC:FF,FS:GH,GP:GU,HH:HY").EntireColumn.Hidden = False
    Range("K:L,AM:AW,DC:EA,EH:FB,FG:FR,GI:GO,GV:HG,HZ:ID").EntireColumn.Hidden = True
    
Else
    
    Range("A:J,M:O,Q:AL,AX:DB,EB:EG,FC:FF,FS:GH,GP:GU,HH:HY").EntireColumn.Hidden = True
    Range("K:L,AM:AW,DC:EA,EH:FB,FG:FR,GI:GO,GV:HG,HZ:ID").EntireColumn.Hidden = False
    
End If
ActiveSheet.Protect , AllowFiltering:=True

Application.Goto Range("A1"), True
End Sub

Thinking about it two buttons may be ok.

 

[CameronC]

The goal is to be able to toggle between 8 different views of a worksheet using buttons in the sheet. Buttons are locked in place so they will not move with columns appearing/disappearing. For one of the buttons this code works for the macro but when I try to change the range for the different buttons it no longer works.

Sub Standard_View()

'Unprotect Worksheet
ActiveSheet.Unprotect

'Unhide All Columns
Columns("A:HY").EntireColumn.Hidden = False

'Hide Selection
Range("K:L,P:P,AM:AW,DC:EA,EH:FB,FG:FR,GI:GO,GV:HG").EntireColumn.Hidden = True


'Protect Worksheet
ActiveSheet.Protect , AllowFiltering:=True


End Sub