Hi,

what does this gives an error.

Sub compare()
Sheets("new").Select
lr = Cells(65536, 1).End(xlUp).Row
lc = Range("IV1").End(xlToLeft).Column

'Range(Cells(1, 1), Cells(lr, lc)).Select


Sheets("old").Select
Range("A11044").AdvancedFilter Action:=xlFilterInPlace,
CriteriaRange:= _
Sheets("new").Range(lr, lc), Unique:=False


please help
Thanks!
Boss

Hi
Your criteria range
Sheets("new").Range(lr, lc)

is one cell.
Do you mean

Sheets("new").Select
lr = Cells(65536, 1).End(xlUp).Row
lc = Range("IV1").End(xlToLeft).Column

With Sheets("new")
'note the dots. No need to select and it won't help
Set CritRange = .Range(.Cells(1, 1), .Cells(lr, lc))
end with
Sheets("old").Select
Range("A11044").AdvancedFilter Action:=xlFilterInPlace,
CriteriaRange:= _
Critrange, Unique:=False

regards
Paul


On Dec 15, 12:00*pm, Boss <B...@discussions.microsoft.com> wrote:
> Hi,
>
> what does this gives an error.
>
> Sub compare()
> Sheets("new").Select
> * * lr = Cells(65536, 1).End(xlUp).Row
> * * lc = Range("IV1").End(xlToLeft).Column
>
> 'Range(Cells(1, 1), Cells(lr, lc)).Select
>
> Sheets("old").Select
> * * Range("A11044").AdvancedFilter Action:=xlFilterInPlace,
> CriteriaRange:= _
> * * * Sheets("new").Range(lr, lc), Unique:=False
>
> please help
> Thanks!
> Boss

Too good..

Defining range is something i thought but unable to code. Thanks for it..

Mean While i did something like this

Sub compare()
Sheets("new").Select
lr = Cells(65536, 1).End(xlUp).Row
lc = Range("IV1").End(xlToLeft).Column


Sheets("old").Range("A11044").AdvancedFilter Action:=xlFilterInPlace,
CriteriaRange:= _
Range(Cells(1, 1), Cells(lr, lc)), Unique:=False

Sheets("old").Select
Range("a1").Select

End Sub

Thanks for your help
Boss

"(E-Mail Removed)" wrote:

> Hi
> Your criteria range
> Sheets("new").Range(lr, lc)
>
> is one cell.
> Do you mean
>
> Sheets("new").Select
> lr = Cells(65536, 1).End(xlUp).Row
> lc = Range("IV1").End(xlToLeft).Column
>
> With Sheets("new")
> 'note the dots. No need to select and it won't help
> Set CritRange = .Range(.Cells(1, 1), .Cells(lr, lc))
> end with
> Sheets("old").Select
> Range("A11044").AdvancedFilter Action:=xlFilterInPlace,
> CriteriaRange:= _
> Critrange, Unique:=False
>
> regards
> Paul
>
>
> On Dec 15, 12:00 pm, Boss <B...@discussions.microsoft.com> wrote:
> > Hi,
> >
> > what does this gives an error.
> >
> > Sub compare()
> > Sheets("new").Select
> > lr = Cells(65536, 1).End(xlUp).Row
> > lc = Range("IV1").End(xlToLeft).Column
> >
> > 'Range(Cells(1, 1), Cells(lr, lc)).Select
> >
> > Sheets("old").Select
> > Range("A11044").AdvancedFilter Action:=xlFilterInPlace,
> > CriteriaRange:= _
> > Sheets("new").Range(lr, lc), Unique:=False
> >
> > please help
> > Thanks!
> > Boss
>
>

Hi in huge data the macro is failing. cound you please hepl..

Thanks!
Boss

"Boss" wrote:

> Too good..
>
> Defining range is something i thought but unable to code. Thanks for it..
>
> Mean While i did something like this
>
> Sub compare()
> Sheets("new").Select
> lr = Cells(65536, 1).End(xlUp).Row
> lc = Range("IV1").End(xlToLeft).Column
>
>
> Sheets("old").Range("A11044").AdvancedFilter Action:=xlFilterInPlace,
> CriteriaRange:= _
> Range(Cells(1, 1), Cells(lr, lc)), Unique:=False
>
> Sheets("old").Select
> Range("a1").Select
>
> End Sub
>
> Thanks for your help
> Boss
>
> "(E-Mail Removed)" wrote:
>
> > Hi
> > Your criteria range
> > Sheets("new").Range(lr, lc)
> >
> > is one cell.
> > Do you mean
> >
> > Sheets("new").Select
> > lr = Cells(65536, 1).End(xlUp).Row
> > lc = Range("IV1").End(xlToLeft).Column
> >
> > With Sheets("new")
> > 'note the dots. No need to select and it won't help
> > Set CritRange = .Range(.Cells(1, 1), .Cells(lr, lc))
> > end with
> > Sheets("old").Select
> > Range("A11044").AdvancedFilter Action:=xlFilterInPlace,
> > CriteriaRange:= _
> > Critrange, Unique:=False
> >
> > regards
> > Paul
> >
> >
> > On Dec 15, 12:00 pm, Boss <B...@discussions.microsoft.com> wrote:
> > > Hi,
> > >
> > > what does this gives an error.
> > >
> > > Sub compare()
> > > Sheets("new").Select
> > > lr = Cells(65536, 1).End(xlUp).Row
> > > lc = Range("IV1").End(xlToLeft).Column
> > >
> > > 'Range(Cells(1, 1), Cells(lr, lc)).Select
> > >
> > > Sheets("old").Select
> > > Range("A11044").AdvancedFilter Action:=xlFilterInPlace,
> > > CriteriaRange:= _
> > > Sheets("new").Range(lr, lc), Unique:=False
> > >
> > > please help
> > > Thanks!
> > > Boss
> >
> >