VBA to hide a named range

[delexcel]

Hi there,

Can anyone suggest some VBA to hide/unhide a named range based on whether a workbook is 'in development' or not?

At the moment, I have the following 'in development' code to protect and unprotect all sheets in one go.

Ideally I would also like to hide specific named ranges when it is not in development, and then unhide them when I am in development.

Public Sub SetProtectWorksheets()

Dim oSheet As Worksheet

For Each oSheet In ActiveWorkbook.Worksheets
    
    If IN_DEVELOPMENT = True Then
            oSheet.Unprotect PROTECT_PASSWORD
            oSheet.EnableSelection = xlNoRestrictions
            ActiveWindow.DisplayWorkbookTabs = True
    End If
    
    If IN_DEVELOPMENT = False Then
            oSheet.Protect PROTECT_PASSWORD
            oSheet.EnableSelection = xlUnlockedCells
            ActiveWindow.DisplayWorkbookTabs = False
    End If
    
Next 'oSheet

End Sub

 

[mikerickson]

What do you mean hide?

Hide the name from the list of names in the name manager? This code will do that

Names("myNamedRange").Visible = False

If you mean hide the cells, you have to decide if you want to hide the column or the row, but you have to hide all the cells in those columns or rows

Range("myNamedRange").EntireColumn.Hidden = True
' or use the .EntireRow property

 

[delexcel]

Hi Mike,

I have four ranges that I don't want hidden, they are A1:I15, A31:I45, A61:I75, A91:I105.

However between each of the above ranges (i.e. 16:30, 46:60, 76:90, and 106:120) and to the right of column I (say J:AD), I have all of the data I would like to hide.

I have created a named range which selects all of the rows/columns I would like to hide.

 

[Peter_SSs]

As Mike has indicated, to hide a range you have to hide the range's entire row(s) or entire column(s) or both. So if your named range was, say, the union of A16:I30, A46:I60, A76:I90, J1:AD105 then if you hide the entire rows or columns of that range you will also hide the ranges that you do not want hidden.

One way to deal with this would be to name 2 ranges ..
- the union of A16:A30, A46:A60, A76:A90 as RowsToHide
- J1:AD1 as ColumnsToHide

Then use some code like this:

Sub Hide_Ranges()
    Range("RowsToHide").EntireRow.Hidden = True
    Range("ColumnsToHide").EntireColumn.Hidden = True
End Sub

 

[delexcel]

Hi Peter,

Thank you for your assistance.

I have encountered the following error:

"Unable to set the hidden property of the range class"

I have done a forum/google search for others who have experienced similar problems but wasnt able to fix the problem unfortunately.

Any assistance would be greatly appreciated!

 

[Peter_SSs]

I have encountered the following error:

"Unable to set the hidden property of the range class"

1. On which line of code?


2. Did you set up your named ranges like this?

One way to deal with this would be to name 2 ranges ..
- the union of A16:A30, A46:A60, A76:A90 as RowsToHide
- J1:AD1 as ColumnsToHide

3. If you used different ranges and/or range names, please advise what you used and describe how you set up the named ranges.

 

[delexcel]

It stops on the Range("RowsToHide").EntireRow.Hidden = True

Setup the named ranges exactly as you stated.

I know the actual ranges work as I tested them another way.

 

[Peter_SSs]

1. Comment out that line and run the code again to see if the column part works.

2. Run this code and report the result

<font face=Courier New><br><SPAN style="color:#00007F">Sub</SPAN> Test1()<br>    MsgBox Range("RowsToHide").Address(0, 0)<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>

For me it is:
A16:A30,A46:A60,A76:A90

 

[delexcel]

Hi Peter,

Thank you for replying to my post (and apologies for my tardy response).

I haven't tried the two tests you provided as after shutting down excel and starting again the problem didn't exist anymore - strange!

However I have noted your suggestions and will use if I come across a similar problem in the future.

Thanks again.

 

[Ajqtrz]

Here's a method that is much easier to understand and works for single cells, ranges, rows and columns. First, create a single cell named range outside the targeted area. I call mine (something)toggle because it is where I'll store the number used to hide/unhide the desired cells. Now put a number in that cell. In the end anything greater than 0 is what I use (more on this later). Now select the cell/cells you wish to hide. Again, it can be a single cell, a block of cells, a row or a column. Use conditional formatting to turn any text in your selection to white (i.e. the background color) and whenever or wherever you need them hidden use the VBA code Worksheets("MyWorksheet").Range("(whatever you called it)toggle").Value = 0 or VBA code Worksheets("MyWorksheet").Range("(whatever you called it)toggle").Clear.

The advantage is that you can set the contents to not only hide but to be some other color as needed since you can use various numbers. For instance, you want the thing "greyed out" you can set your toggle cell to 2 and use a second level conditional formatting to get it "greyed out." Since the toggle cell has as many choices as there are numbers you are only limited to the three levels of conditional formatting, i.e. you can have three different formats, one of which is "hidden".

Finally, a caveat. This will not remove -- i.e.hide cells -- when you print. The contents will be hidden, but the space will be retained. So if you wish to hide an entire row when printing (i.e. to 'remove' the blank space, for instance), you will need another method for that. Alas, that will be another post. So much Excel, so little time!

AJ