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

--
Thanks!
Stefi

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

HTH

Bob

"Stefi" wrote in message
...
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 = ???

--
Thanks!
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:

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

HTH

Bob

"Stefi" wrote in message
...
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 = ???

--
Thanks!
Stefi



.

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

HTH

Bob

"Stefi" wrote in message
...
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:

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

HTH

Bob

"Stefi" wrote in message
...
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 = ???

--
Thanks!
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:

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

HTH

Bob

"Stefi" wrote in message
...
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:

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

HTH

Bob

"Stefi" wrote in message
...
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 = ???

--
Thanks!
Stefi



.



.

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

"Stefi" skrev i meddelelsen
...
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 = ???

--
Thanks!
Stefi

On Feb 16, 4:23*pm, Stefi wrote:
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 = ???

--
Thanks!
Stefi

untried, but perhaps:

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