range of unique cells

[Stefi]

Hi All,

If I make a unique list with advanced filter, how can I define the range
consisting of the unique cell, e.g. if in Range("A8:A23") cells A8, A9, A11,
A15, A16 remained visible after fitering then what is the statement to define
a range consisting A8, A9, A11, A15, A16 cells?


Range("A8:A23").AdvancedFilter Action:=xlFilterInPlace, Unique:=True
set uniqcells = ???

 

[Bob Phillips]

Set uniqcells = Range("A8:A23").SpecialCells(xlCellTypeVisible)

HTH

Bob

 

[Per Jessen]

Hi

Set uniqcells = Range("A8:A23").SpecialCells(xlCellTypeVisible)

Just remeber that when using a filter, excel expect A8 (first row) to be a
header, and not a part of the filtered values.

Regards,
Per

 

[Jef Gorbach]

Hi All,

If I make a unique list with advanced filter, how can I define the range
consisting of the unique cell, e.g. if in Range("A8:A23") cells A8, A9, A11,
A15, A16 remained visible after fitering then what is the statement to define
a range consisting A8, A9, A11, A15, A16 cells?

Range("A8:A23").AdvancedFilter Action:=xlFilterInPlace, Unique:=True
set uniqcells = ???

untried, but perhaps:

Range("A8:A23").AdvancedFilter Action:=xlFilterInPlace, Unique:=True
Set uniqcells = range("A8:A23").SpecialCells(xlCellTypeVisible)

 

[Stefi]

Thanks to all of you, it worked.

I mislead myself when I tried this solution by myself and wanted to check
the result with uniqcells.rows.count which gave 3 and it was wrong. Later I
found out that uniqcells.count gives the right result, while
uniqcells.rows.count gives the number of rows in the first contiguous part of
uniqcells, it's really 3, but I still don't understand the reason of
differing the number of rows and the number of cells in a one column wide
range. Could you explain it?

Thanks!
Stefi



„Bob Phillips†ezt írta:

 

[Bob Phillips]

In a one column range they should be the same. Are you sure your range was
just one column wide?

HTH

Bob

 

[Stefi]

Hi Bob,

I checked again: the range was definitely one column wide.

uniqcells.rows.count gave 3 while uniqcells.count gave 8 which was right.

When I increased the width to 2 for testing purposes, uniqcells.rows.count
gave still 3 while uniqcells.count gave 2*8=16 which was also right.

--
Regards!
Stefi



„Bob Phillips†ezt írta: