Hiding a range of cells

[Melvin]

Hi, I am trying to get a series of columns hidden but when I do this, it
selects the columns H to O as H1-O1 are merged together as a cell. I can't
seem to set the rngMulti.Hidden property either. Any help would be hot!!

Sub Test()
Dim rng1 As Range, rng2 As Range, rngMulti As Range
Worksheets("Sheet 1").Activate
Set rng1 = Range("H:I")
Set rng2 = Range("K:L")
Set rngMulti = Union(rng1 , rng2)
rngMulti.Select
End Sub

Thanks
Melvin

 

[OJ]

Hi,
you wont get anywhere with this while your cells are merged. I think
most Excel developers agree that merged cells should really be avoided,
as most of the effects they produce can be acieved via other methods
(Centre across selection etc...). You certainly cannot hide a column
with a merged cell in it without hiding the other column(s) involved in
that merge as well...

If you do unmerge the cells then this should work...

Sub Test()
Dim rngMulti as Range
Set rngMulti = range("H:I","K:L")
rngMulti.EntireColumn.Hidden = True
End Sub

Hth,
OJ

 

[Tom Ogilvy]

Don't use select. This worked fine for me with H1:O1 merged:

Sub Test()
Dim rng1 As Range, rng2 As Range
With Worksheets("Sheet1")
.Columns.Hidden = False
Set rng1 = .Range("H:I")
Set rng2 = .Range("K:L")
End With
Union(rng1, rng2).EntireColumn.Hidden = True
End Sub

 

[OJ]

Oops...so it does!! Well done Tom!