special cell selection

  • Thread starter Thread starter raj
  • Start date Start date
R

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.
 
NEVERMIND. I figured it out (duh):

Application.Intersect(Range("B:B") _
.SpecialCells(xlCellTypeVisible), _
Range(ActiveSheet.UsedRange.Address)).Select
 
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.
 
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.)
 
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.

Dave said:
Yep.

I saw your response and said "doh". I hope Tom doesn't read mine.
Click to expand...
 
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.
 
Back
Top