Hiding columns in a named range

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

malcomio - 28 Apr 2008 12:21 GMT

Is there a way of hiding columns in named ranges in VBA? I've got a load of
columns that I want to show and hide, but I might want to add other columns
in the worksheets later, so named ranges would make life easier.

I would have thought it was simple, but I just get an error that I'm "unable
to set hidden property of range class"

Reply to this Message

paul.robinson@it-tallaght.ie - 28 Apr 2008 12:32 GMT

Hi
Either of these will hide column 2 in range "tester"

ActiveSheet.Range("tester").Columns(2).Hidden = True

or
ActiveSheet.Range("tester").Cells(1,2).EntireColumn.Hidden = True

There is no Hide method, so this won't work

ActiveSheet.Range("tester").Columns(2).Hide

no matter how tempting it looks!
regards
Paul

On Apr 28, 12:21 pm, malcomio <malco...@discussions.microsoft.com>
wrote:

> Is there a way of hiding columns in named ranges in VBA? I've got a load of
> columns that I want to show and hide, but I might want to add other columns
> in the worksheets later, so named ranges would make life easier.
>
> I would have thought it was simple, but I just get an error that I'm "unable
> to set hidden property of range class"

Reply to this Message

paul.robinson@it-tallaght.ie - 28 Apr 2008 12:35 GMT

Forgot to mention
You may also find this useful

With ActiveSheet.Range("tester")
.Range(.Columns(2), .Columns(4)).Hidden = True
End With

which will hide columns 2 to 4.
regards
Paul

On Apr 28, 12:32 pm, paul.robin...@it-tallaght.ie wrote:

> Hi
> Either of these will hide column 2 in range "tester"
[quoted text clipped - 23 lines]
>
> - Show quoted text -

Reply to this Message

malcomio - 28 Apr 2008 13:05 GMT

thanks Paul

Reply to this Message

malcomio - 28 Apr 2008 14:25 GMT

do you know if pivot tables on a worksheet cause problems? maybe i'm having
problems with worksheet and workbook scope, but I have a range of columns K:V
called "wholeyear", scoped to each worksheet, and when I run the procedure it
hides columns U:AF.

Very strange, especially as this isn't a named range at all.

Reply to this Message

paul.robinson@it-tallaght.ie - 28 Apr 2008 15:29 GMT

Hi
Are you hiding columns relative to some range

e.g. Range("B1:C1").Columns("B:B").Hidden = true

will hide the second column in "B1:C1" i.e. column C of the worksheet.
regards
Paul

On Apr 28, 2:25 pm, malcomio <malco...@discussions.microsoft.com>
wrote:

> do you know if pivot tables on a worksheet cause problems? maybe i'm having
> problems with worksheet and workbook scope, but I have a range of columns K:V
> called "wholeyear", scoped to each worksheet, and when I run the procedure it
> hides columns U:AF.
>
> Very strange, especially as this isn't a named range at all.

Reply to this Message

malcomio - 28 Apr 2008 16:06 GMT

I am hiding columns within a range, and those columns are the values section
of a pivot table.

It seems to be affecting a different range, depending on the number of
columns in the pivot table, even though the named range is set to the values
section. It's very strange. Would you mind if I emailed you an example of the
problem?

Reply to this Message

paul.robinson@it-tallaght.ie - 28 Apr 2008 16:20 GMT

Sure - probably tomorrow when I reply, but I'll reply direct to you.
Paul

On Apr 28, 4:06 pm, malcomio <malco...@discussions.microsoft.com>
wrote:

> I am hiding columns within a range, and those columns are the values section
> of a pivot table.
[quoted text clipped - 3 lines]
> section. It's very strange. Would you mind if I emailed you an example of the
> problem?

Reply to this Message

Don Guillett - 28 Apr 2008 19:39 GMT

try this
Sub hidecolinrng()
'Range("hiderng").Cells(1, 3).EntireColumn.Hidden = False
Range("hiderng").Range(Cells(1, 3), Cells(1, 4)).EntireColumn.Hidden = True
'FALSE
End Sub

Signature

Don Guillett
Microsoft MVP Excel
SalesAid Software
dguillett1@austin.rr.com

> Is there a way of hiding columns in named ranges in VBA? I've got a load
> of
[quoted text clipped - 5 lines]
> "unable
> to set hidden property of range class"

Reply to this Message