Hiding all Columns and Rows EXCEPT named range

[Gregg Wolin]

I'm trying to optimize the code I use to navigate within areas of a worksheet based on button clicks. I currently accomplish this with a macro that unhides the columns and rows representing the destination area, then hides the columns & rows outside such destination which is tedious, especially if i move or modify the dimensions of said destination area.

It occurred to me that it might be more efficient to create a named range that represents the cells that comprise the destination area, then to have the macro simply hide all rows and columns that are OUTSIDE of such named range.

For example, if the worksheet range (B2 : R30 ) is named "Destination1," how would i go about hiding all other columns and rows outside the range? Would such a macro run faster than:

Columns("S:XFD").EntireColumnHidden = True
Rows (31:1045879).EntireRows.Hidden = True

 

[vletm]

Gregg Wolin

Sub Do_It()
    With ActiveSheet
        .Columns.EntireColumn.Hidden = True
        .Rows.EntireRow.Hidden = True
        Set destination1 = .Range("B2:R30")
        destination1.EntireColumn.Hidden = False
        destination1.EntireRow.Hidden = False
    End With
End Sub

 

[Gregg Wolin]

Gregg Wolin

Sub Do_It()
    With ActiveSheet
        .Columns.EntireColumn.Hidden = True
        .Rows.EntireRow.Hidden = True
        Set destination1 = .Range("B2:R30")
        destination1.EntireColumn.Hidden = False
        destination1.EntireRow.Hidden = False
    End With
End Sub

I get a runtime 1004 error "Unable to set the Hidden property of the Range class" .

When i debug it is tripping at .Columns.EntireColumn.Hidden = True

Not sure if this is the problem but since this worksheet has a number of different operative areas between which i navigate, there will always be certain sets of columns and rows that are hidden when this macro is run.

 

[vletm]

Gregg Wolin
I tested it with empty worksheet ... and it worked.
Do You have there any merged cells?
... and ...
as always ... without a sample Excel-file ... others can test/verify something.