hide sheets in named range array

Question

i have a dynamic named range which provides the sheets names in a list (let's say A2 = Austria, A3 = Germany, A4 = Poland) This list can vary in size depending on the user so I have an offset in the named range to capture all the cells in column A with a name in it.

I would like to use an IF statement to veryhide certain sheets based on this named range.

something like:

Public Sub test()
    Sheets(Array(Range("MyRange"))).Visible = xlVeryHidden
End Sub

with the above i get the following error: Run-time error '13': Type mismatch

any suggestions please.

Thank you Paul

Charles Williams · Accepted Answer · 2011-02-01 11:14:12Z

1

You could just loop on the Named range

Sub Test()
dim oCell as range
for each oCell in Range("MyRange)
Sheets(oCell.value).visible=xlVeryHidden
next oCell
end sub

answered Feb 1, 2011 at 11:14

Charles Williams

23.6k5 gold badges40 silver badges39 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Simon Cowen · Accepted Answer · 2011-02-01 11:13:55Z

1

Sheets cannot take an array of names as it's argument, it's declared as Sheets(index), so you'll need to loop through the names and hide the sheets one-by-one unfortunately.

Try:

Sub test()
    Dim rngCell As Range
    For Each rngCell In Range("MyRange")
        Sheets(rngCell.Value).Visible = xlSheetVeryHidden
    Next rngCell
End Sub

answered Feb 1, 2011 at 11:13

Simon Cowen

1,90317 silver badges20 bronze badges

Comments

Tim Williams · Accepted Answer · 2011-02-02 07:01:14Z

0

You can pass an array to Sheets(), but if you're getting it from a vertical range you need to transpose it first.

This works for me:

ThisWorkbook.Sheets(Application.Transpose(Sheet3.Range("SHEETS").Value)).Visible = _ xlSheetHidden

Where SHEETS contains the values "Sheet1" and "Sheet2". Unfortunately for some reason it only seems to work with xlSheetHidden and not with xlSheetVeryHidden ....

Tim

answered Feb 2, 2011 at 7:01

Tim Williams

169k8 gold badges104 silver badges143 bronze badges

Collectives™ on Stack Overflow

hide sheets in named range array

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related