Hi,
I have 20 sheets in a work bookwith up to 12 that will be hidden depending on the number of sheets that a particular site will use (i.e. small sites may only need to use 4 sheets, whilst larger ones may need all 12).
I already have VBA that give me a TRUE/FALSE result if a sheet is hidden, and this will then remove the name of the hidden sheet from the named range used by the data list. Unfortunately, any sheet names that are blank will also appear as blank in the drop down list.
I am currently using...
=OFFSET($A$1,0,0,COUNTA($A:$A),1)
to give me the list, but can it be changed to remove all the of the blank names, or is there some VBA that will do this?
Any help would be appreciated.
I have 20 sheets in a work bookwith up to 12 that will be hidden depending on the number of sheets that a particular site will use (i.e. small sites may only need to use 4 sheets, whilst larger ones may need all 12).
I already have VBA that give me a TRUE/FALSE result if a sheet is hidden, and this will then remove the name of the hidden sheet from the named range used by the data list. Unfortunately, any sheet names that are blank will also appear as blank in the drop down list.
I am currently using...
=OFFSET($A$1,0,0,COUNTA($A:$A),1)
to give me the list, but can it be changed to remove all the of the blank names, or is there some VBA that will do this?
Any help would be appreciated.
