hide a column macro - with a twist

[John Caines]

Hello All.
I found a bit of code here to hide /toggle some rows which was;

Sub ToggleRows()
Rows("23:37").Select
 If Selection.EntireRow.Hidden = True Then
 Selection.EntireRow.Hidden = False
 Else: Selection.EntireRow.Hidden = True
 End If
End Sub

Great!! Works fine,,,,so, on the same sheet I want to also toggle some columns at the end (Right hand side) of my worksheet.
So, I think's to myself,,, let's just replace the words 'Row' With 'Column' from the code above.

So I've tried;

Sub ToggleColumns()
Columns("AN:AT").Select
 If Selection.EntireColumn.Hidden = True Then
 Selection.EntireColumn.Hidden = False
 Else: Selection.EntireColumn.Hidden = True
 End If
End Sub

Problem,,I get a Run-time error '1004'....???
Unable to set Hidden property of the Range clas
?? Not a clue what this means.
\The line that is highlighted yellow on the debug is

: Selection.EntireColumn.Hidden = True

As a note,, the bottom of my sheet from Row 38 I have hidden (Don't want to show all cells below this row as none are being used). Also I have hidden the columns from Column AV onwards, as I don't want to see any columns after AU.
So not sure if this has something to do with it/ sub not working??

Can anybody help me out here? I'm sure it's something quite easy, but it's only easy when you know how!

I've tried googling,,, what a mess,,, sent all over the net, not really sure what I'm looking at as everyone has slightly different problems.
Hope someone can help

Many thanks
JohnC

 

[Rick Rothstein]

So from the above,, maybe the code.....

Private Sub ToggleColumns_Click()
If Columns("AO:AU").EntireColumn.Hidden = True Then
Columns("AO:AU").EntireColumn.Hidden = False
Else: Columns("AO:AU").EntireColumn.Hidden = True
End If
End Sub

should now be....

Private Sub ToggleColumns_Click()
ActiveSheet.Unprotect ""
If Columns("AO:AU").EntireColumn.Hidden = True Then
Columns("AO:AU").EntireColumn.Hidden = False
Else: Columns("AO:AU").EntireColumn.Hidden = True
End If
ActiveSheet.Protect ""
End Sub

Now that you have things working, I'll make the suggestion I made back in Message #2 again... you can replace what I highlighted in red above with this single line of code...

[table="width: 500"]
[tr]
	[td]Columns("AO:AU").EntireColumn.Hidden = Not Columns("AO:AU").EntireColumn.Hidden[/td]
[/tr]
[/table]

 

[John Caines]

Hello Rick,
Many thanks for your reply.

Yes, I remember your reply, I will change it, it is shorter & simpler code.
i've got it working again,, (I had some cell references that were wrong,, and it just broke it!)
All working ok now.
I'll change it in the morning Rick, off to bed now.
Need to get off this laptop, my eyes are burning again!
I think I didn't use it last time as I had an issue with my sheet when I originally tried to use it (No fault of your code, an error in my formatting!)

Many thanks again for your reply Rick, it is very much appreciated.
Tomorrow it w
Yours sincerely
John C

 

[John Caines]

Hi Rick,
Not sure what's happened here,, I did reply about an hr ago on the forum here saying that I've now altered it to your suggestion, but it doesn't seem to have been saved in MrExcel system! ,,, strange.

So just to confirm.

Yes,, I've done as you've said, and it seems to be working fine, cleaner shorter code doing the same job!

Just to illustrate what I have now, here's a screenshot in the VBA;
https://www.dropbox.com/s/4bnxxd5l9kphrvs/What-I-Have-Now-Rick.jpg?dl=0

Private Sub Expand_Click()
ActiveSheet.Unprotect ""
Rows("23:38").EntireRow.Hidden = Not Rows("23:38").EntireRow.Hidden
ActiveSheet.Protect ""
End Sub

Private Sub ToggleColumns_Click()
ActiveSheet.Unprotect ""
Columns("AO:AU").EntireColumn.Hidden = Not Columns("AO:AU").EntireColumn.Hidden
ActiveSheet.Protect ""
End Sub

All seems fine Rick. Great stuff.

Many thanks again for everyones help here.
My sheet is now about 80% done and looking good!

Yours sincerely
A very grateful
John C