Problem Unhidding columns/rows in excel using VBA

[Dan Hatola]

I am having trouble with some VBA code. The goal was to write code that
would hide all columns or rows and then unhide a select few.

Here was the first attempt:
Sub UnhideCol()
With ActiveSheet
.Columns.Hidden = True 'Hide all columns
.Columns("A:B").Hidden = False 'Unhide some range of columns
End With
End Sub

The result is that all columns hidden instead of having ("A:B") unhidden.

Here is another try:
Sub UnhideCol2()
With ActiveSheet
.Range("A:B").Columns.Hidden = True
Set MyRange = .Cells.SpecialCells(xlVisible) 'MYRange("C:IV")
.Columns.Hidden = True 'Hide all columns
MyRange.Columns.Hidden = False 'Unhide MyRange("C:IV")
End With
End Sub

This successfully unhides ("C:IV").

Here is the same macro with only the range changed from ("A:B") to ("C:IV").
Sub UnhideCol2()
With ActiveSheet
.Range("C:IV").Columns.Hidden = True
Set MyRange = .Cells.SpecialCells(xlVisible) 'MYRange("A:B")
.Columns.Hidden = True 'Hide all columns
MyRange.Columns.Hidden = False 'Unhide MyRange("A:B")
End With
End Sub

The result is that all columns are hidden rather than having ("A:B") unhidden.
There seems to be a lot of inconsistency with how VBA/Excel are handling the
code.

It is even stranger with Rows:

Sub UnhideRow()
With ActiveSheet
.Rows.Hidden = True 'Hide all rows
.Rows("1:2").Hidden = False 'Unhide some range of rows
End With
End Sub

This creates a run-time error '-2147417848 (80010108)' Method 'Hidden' of
object 'Range' failed

I am using Excel 2000, but it the run-time error occured when I tested it in
Excel 2003, too. Any insight would be greatly appreciated.

 

[Dave Peterson]

The first attempt worked for me. I did have to scroll to the left to see
columns A:B, though.

 

[Dan Hatola]

Thanks for checking it out. I tried it on another computer and I had luck
with my first three subroutines (all column related).

However, I am still getting the run-time error when I try to hide the rows.

Sub UnhideRow()
With ActiveSheet
.Rows.Hidden = True 'Hide all rows
.Rows("1:2").Hidden = False 'Unhide some range of rows
End With
End Sub

 

[Dave Peterson]

I've never had real good luck with hiding and unhiding rows/columns.

The usedrange seems to grow with each iteration.

I think I wouldn't do this.

But this did work for me:

Option Explicit
Sub UnhideRow()
With ActiveSheet
.Rows("1:2").Hidden = False
.Rows("3:" & .Rows.Count).Hidden = True
End With
End Sub

 

[NickHK]

Dan,
Not sure if it applies to you, but I have noticed that if you have any
controls on the WS, there must be enough columns/rows visible for these to
remain. i.e. you can't hide all rows/columns if you have controls or
pictures on the WS. not sure about other XL object like query table, pivot
table, validation etc, but you can test.
Although this give a 1004 error.

NickHK