special cell selection
 

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.

special cell selection
 

NEVERMIND. I figured it out (duh):

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


-----Original Message-----
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.
.

special cell selection
 

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.

--
Regards,
Tom Ogilvy

raj wrote in message
...
NEVERMIND. I figured it out (duh):

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


-----Original Message-----
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.
.

special cell selection
 

or

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

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


raj wrote:

NEVERMIND. I figured it out (duh):

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

-----Original Message-----
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.
.


--

Dave Peterson

special cell selection
 

UsedRange, of course, is redundant.

--
Regards,
Tom Ogilvy

Dave Peterson wrote in message
...
or

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

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


raj wrote:

NEVERMIND. I figured it out (duh):

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

-----Original Message-----
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.
.


--

Dave Peterson

special cell selection
 

Yep.

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

Tom Ogilvy wrote:

UsedRange, of course, is redundant.

--
Regards,
Tom Ogilvy

Dave Peterson wrote in message
...
or

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

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


raj wrote:

NEVERMIND. I figured it out (duh):

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

-----Original Message-----
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.
.


--

Dave Peterson


--

Dave Peterson

special cell selection
 

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 Peterson wrote:
Yep.

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

Tom Ogilvy wrote:

UsedRange, of course, is redundant.

--
Regards,
Tom Ogilvy

Dave Peterson wrote in message
...

or

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

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


raj wrote:

NEVERMIND. I figured it out (duh):

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


-----Original Message-----
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.
.


--

Dave Peterson





--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

special cell selection
 

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(xlVi sible)
On Error goto 0

although this assumes column A is in the used range.
--
Regards,
Tom Ogilvy


Debra Dalgleish wrote in message
...
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 Peterson wrote:
Yep.

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

Tom Ogilvy wrote:

UsedRange, of course, is redundant.

--
Regards,
Tom Ogilvy

Dave Peterson wrote in message
...

or

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

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


raj wrote:

NEVERMIND. I figured it out (duh):

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


-----Original Message-----
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.
.


--

Dave Peterson





--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html