special cell selection

[raj]

Hello. I hope someone can help me with this please.

I would like to be able to select the visible cells in the
used range of column "B" in a sheet.

Ideally, it would do so in a single line of VBA code.
Your example code would be most appreciated. Thanks in
advance.

 

[raj]

NEVERMIND. I figured it out (duh):

Application.Intersect(Range("B:B") _
.SpecialCells(xlCellTypeVisible), _
Range(ActiveSheet.UsedRange.Address)).Select

 

[Tom Ogilvy]

On Error Resume Next
Columns(2).SpecialCells(xlVisible).Select
On Error goto 0

might be a bit simpler.

Special cells restricts itself to the usedrange, so you don't need to do the
intersect.

 

[Dave Peterson]

or

With ActiveSheet
Intersect(.Range("b:b").SpecialCells(xlCellTypeVisible), _
.UsedRange).Select
End With

(and watch out. if there are no visible cells, then you'll get an error.)

 

[Tom Ogilvy]

UsedRange, of course, is redundant.

 

[Dave Peterson]

Yep.

I saw your response and said "doh". I hope Tom doesn't read mine.

 

[Debra Dalgleish]

An interesting note (well to me anyway <g>) --

If any cell outside the used range is currently selected, your code
selects just the visible cells in the the used range in column B, and
Tom's selects all visible cells in column B.

Yep.

I saw your response and said "doh". I hope Tom doesn't read mine.

 

[Tom Ogilvy]

I believe I am wrong on this - so my apologies to Dave and the OP.

Anyway
On Error Resume Next
set rng = Activesheet.UsedRange.Columns(2).Specialcells(xlVisible)
On Error goto 0

although this assumes column A is in the used range.