Stefi
12/19/2006 2:35:00 PM
Hi Bob,
I couldn't ignore row 1 because when I tried
Set osszsor = Range("B2:B" & darab)
osszsor.AdvancedFilter Action:=xlFilterInPlace, Unique:=True
instead of
Set osszsor = Range("B1:B" & darab)
osszsor.AdvancedFilter Action:=xlFilterInPlace, Unique:=True
Excel considered row 2 like heading and involved in the unique range both
row 1 and row 2. B2 being a data cell with the same value as B3 I got two of
these values within the range and lost uniqueness.
I could create the requested range only by rebuilding it with this piece of
code:
For Each allomas In allkodok
allomas.Select
If allomas.Address(False, False) = "B1" Then
Set allknohead = Nothing
Else
If allknohead Is Nothing Then
Set allknohead = allomas
Else
Set allknohead = Union(allknohead, allomas)
End If
End If
Next allomas
where allknohead became the requested unique range. Do you know a simpler
method?
Autofilter does work on a non-contiguous range according to my experiences.
Regards,
Stefi
â??Bob Phillipsâ? ezt Ãrta:
> Didn't realise allkodok is not contiguous. You will need to ignore row 1
> when you create allkodok.
>
> BTW, does autofilter work on a non-contiguous range?
>
> --
> ---
> HTH
>
> Bob
>
> (change the xxxx to gmail if mailing direct)
>
>
> "Stefi" <Stefi@discussions.microsoft.com> wrote in message
> news:F4C26E60-1BD0-43AF-827D-25291CFF5935@microsoft.com...
> > Sorry Bob, it still doesn't work. I get a hint saying:
> >
> > allkodok.Offset(1, 0).Resize...<Application-defined or object-defined
> > error>
> >
> > It seems as if Offset wouldn't work (perhaps because allkodok is NOT a
> > contiguous range).
> >
> > Regards,
> > Stefi
> >
> > "Bob Phillips" ezt Ãrta:
> >
> >> Set allkodok = allkodok.Offset(1, 0).Resize(allkodok.Rows.Count - 1)
> >>
> >>
> >> --
> >> ---
> >> HTH
> >>
> >> Bob
> >>
> >> (change the xxxx to gmail if mailing direct)
> >>
> >>
> >> "Stefi" <Stefi@discussions.microsoft.com> wrote in message
> >> news:DAA15D78-8E24-43D1-9426-2150D099F952@microsoft.com...
> >> > Hi All,
> >> >
> >> > I created a unique range with this piece of code:
> >> > Dim allkodok As Range, osszsor As Range
> >> > darab = Columns("B:B").End(xlDown).Row
> >> > Set osszsor = Range("B1:B" & darab)
> >> > osszsor.AdvancedFilter Action:=xlFilterInPlace, Unique:=True
> >> > Set allkodok = osszsor.SpecialCells(xlVisible)
> >> >
> >> > My problem is that range "allkodok" contains the header row (cell B1)
> >> > and
> >> > I
> >> > want to exclude it. I tried
> >> > Set allkodok = allkodok.Offset(1, 0).Resize(allkodok.Count - 1)
> >> > but it fails. Please help!
> >> >
> >> > Regards,
> >> > Stefi
> >> >
> >>
> >>
> >>
>
>
>