Basic question....syntax on active cells from a filter

If I filter and then want to select only those cells showing and give them a
range name, what's the syntax? I tried xllastrow but that gives everything.
I also tried putting something like ...for every activecell in range but that
also gave me everything.

Is this possible or am I wishing too hard?

Hi,
Try the visible cells instead:
debug.print
ActiveSheet.AutoFilter.Range.SpecialCells(xlCellTypeVisible).Address
--
Regards,
Sébastien
<http://www.ondemandanalysis.com>


"Rominall" wrote:

> If I filter and then want to select only those cells showing and give them a
> range name, what's the syntax? I tried xllastrow but that gives everything.
> I also tried putting something like ...for every activecell in range but that
> also gave me everything.
>
> Is this possible or am I wishing too hard?

So how does it go in the name statement?
ActiveWorkbook.Names.Add Name:="Any", Refersto ????????


"sebastienm" wrote:

> Hi,
> Try the visible cells instead:
> debug.print
> ActiveSheet.AutoFilter.Range.SpecialCells(xlCellTypeVisible).Address
> --
> Regards,
> Sébastien
> <http://www.ondemandanalysis.com>
>
>
> "Rominall" wrote:
>
> > If I filter and then want to select only those cells showing and give them a
> > range name, what's the syntax? I tried xllastrow but that gives everything.
> > I also tried putting something like ...for every activecell in range but that
> > also gave me everything.
> >
> > Is this possible or am I wishing too hard?

something like:
dim rg as range, str as string

set rg=ActiveSheet.AutoFilter.Range.SpecialCells(xlCellTypeVisible)
srt = "'" & rg.parent.name & "'!" & rg.address(true,true)
ActiveWorkbook.Names.Add Name:="Any", Refersto:= "=" & str
' !!! don't forget the '=' in the address string RefersTo eg:
"=sheet1!A1:F10"
--
Regards,
Sébastien
<http://www.ondemandanalysis.com>


"Rominall" wrote:

> So how does it go in the name statement?
> ActiveWorkbook.Names.Add Name:="Any", Refersto ????????
>
>
> "sebastienm" wrote:
>
> > Hi,
> > Try the visible cells instead:
> > debug.print
> > ActiveSheet.AutoFilter.Range.SpecialCells(xlCellTypeVisible).Address
> > --
> > Regards,
> > Sébastien
> > <http://www.ondemandanalysis.com>
> >
> >
> > "Rominall" wrote:
> >
> > > If I filter and then want to select only those cells showing and give them a
> > > range name, what's the syntax? I tried xllastrow but that gives everything.
> > > I also tried putting something like ...for every activecell in range but that
> > > also gave me everything.
> > >
> > > Is this possible or am I wishing too hard?