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("A1:p1044").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("A1:p1044").AdvancedFilter Action:=xlFilterInPlace,
CriteriaRange:= _
Critrange, Unique:=False

regards
Paul


On Dec 15, 12:00*pm, Boss 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("A1:p1044").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("A1:p1044").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

" 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("A1:p1044").AdvancedFilter Action:=xlFilterInPlace,
CriteriaRange:= _
Critrange, Unique:=False

regards
Paul


On Dec 15, 12:00 pm, Boss 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("A1:p1044").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("A1:p1044").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

" 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("A1:p1044").AdvancedFilter Action:=xlFilterInPlace,
CriteriaRange:= _
Critrange, Unique:=False

regards
Paul


On Dec 15, 12:00 pm, Boss 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("A1:p1044").AdvancedFilter Action:=xlFilterInPlace,
CriteriaRange:= _
Sheets("new").Range(lr, lc), Unique:=False

please help
Thanks!
Boss