range of unique cells

S

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 = ???
 
P

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
 
J

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)
 
S

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:
 
B

Bob Phillips

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

HTH

Bob
 
S

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:
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top