activecell.specialcells(xlCellTypeVisible) returns column referenc

[Guest]

Hi, can anyone help?

I want to identify the visible cells (rangeTwo as range) within a given
range (rangeOne as range) ie

set rangeTwo = rangeOne.specialcells(xlCellTypeVisible)

This code seems to work unless rangeOne is a single cell - then rangeTwo is
not contained in rangeOne at all (in fact it returns a reference to all the
visible columns on my worksheet)...why?!!!!


Thanks again in advance

Noel

 

[Tom Ogilvy]

A trick I first saw posted by David McRitchie

set rangeTwo = Intersect(rangeOne.specialcells(xlCellTypeVisible),rangeOne)

 

[Guest]

Thanks for that, very much appreciated.
It seems odd behaviour though - any reason for this?

 

[Tom Ogilvy]

it mimics the manual method. (Edit=>Goto=>Special) If you have one cell
selected, Excel works on the whole sheet. From a manual perspective, it
makes a lot of sense. If you are already there, there is no requirement to
GoTo and it saves having to select the whole sheet if you want to address
the whole sheet.