thanks Again
I have added the IDs line only as I am getting Error "91" for Set ws1
=Sheets("Sheet3") when change it to Set ws1.Sheet3 then it works for me.
"OfficeXp2002 SP3"
My final macro is

Sub AutofilterTwoRanges()
Dim ws1 As Worksheet, ws2 As Worksheet, ws As Worksheet, lngRow As Long
Set ws1 = Sheet3
Set ws2 = Sheet12
Set ws = Sheet18

ws1.Columns(1).AdvancedFilter Action:=xlFilterCopy, _
CopyToRange:=ws.Range("A1"), Unique:=True
lngRow = ws.Cells(Rows.Count, "A").End(xlUp).Row + 1
ws2.Columns(1).AdvancedFilter Action:=xlFilterCopy, _
CopyToRange:=ws.Range("A" & lngRow), Unique:=True
ws.Rows(lngRow).Delete
ws.Range("A1") = "IDs"
ws.Columns(1).Sort Key1:=Range("A2"), Order1:=xlAscending, header:=xlYes
End Sub

Rachid
PS: I do not know where to CLICK YES I am using Microsoft Communities News
Server via Windows Live Mail...

"Jacob Skaria" wrote in message
...
Hi "QuickLearner"

Nice to hear that it helped you..Modified to generate the unique list in
Sheet18. Try and feedback.

Sub AutofilterTwoRanges()
Dim ws1 As Worksheet, ws2 As Worksheet, ws As Worksheet, lngRow As Long
Set ws1 = Sheets("Sheet3")
Set ws2 = Sheets("Sheet12")
Set ws = Sheets("Sheet18")

ws1.Columns(1).AdvancedFilter Action:=xlFilterCopy, _
CopyToRange:=ws.Range("A1"), Unique:=True
lngRow = ws.Cells(Rows.Count, "A").End(xlUp).Row + 1
ws2.Columns(1).AdvancedFilter Action:=xlFilterCopy, _
CopyToRange:=ws.Range("A" & lngRow), Unique:=True
ws.Rows(lngRow).Delete
ws.Range("A1") = "IDs"
ws.Columns(1).Sort Key1:=Range("A2"), Order1:=xlAscending, header:=xlYes
End Sub


If this post helps click Yes
---------------
Jacob Skaria


"QuickLearner" wrote:

Working for me after a bit of Debugging and removing deletion of column B
THANKS JACOB

Sub AutofilterTwoRanges()
Dim ws1 As Worksheet, ws2 As Worksheet, ws As Worksheet, lngRow As Long
Set ws1 = Sheet3 // check this
Set ws2 = Sheet12// check this
Set ws = Sheet18//check this

ws1.Columns(1).AdvancedFilter Action:=xlFilterCopy, _
CopyToRange:=ws.Range("A1"), Unique:=True
lngRow = ws.Cells(Rows.Count, "A").End(xlUp).Row + 1
ws2.Columns(1).AdvancedFilter Action:=xlFilterCopy, _
CopyToRange:=ws.Range("A" & lngRow), Unique:=True
ws.Rows(lngRow).Delete
ws.Columns(1).Sort Key1:=Range("A2"), Order1:=xlAscending, header:=xlYes
End Sub

QUESTION

I have different Headers in both sheets 3&12 in column A i.e CustomerID
and
SupplierID How to change them as ONLY IDs when Macro runs? At the Moment
it
return
CustomerID

Rachid

"Jacob Skaria" wrote in message
...
Hi Hardeep

Try the below macro which will insert a new sheet after sheet2 and
generate
a sorted distinct list. You need to have headers assigned to both the
lists
in sheet1 and sheet2. Incase you are new to macros..

--Set the Security level to low/medium in (Tools|Macro|Security).
--From workbook launch VBE using short-key Alt+F11.
--From menu 'Insert' a module and paste the below code.
--Get back to Workbook.
--Run macro from Tools|Macro|Run <selected macro()

Sub AutofilterTwoRanges()
Dim ws1 As Worksheet, ws2 As Worksheet, ws As Worksheet, lngRow As Long
Set ws1 = Sheets("Sheet1")
Set ws2 = Sheets("Sheet2")

Set ws = Worksheets.Add(After:=ws2)
ws1.Columns(1).AdvancedFilter Action:=xlFilterCopy, _
CopyToRange:=ws.Range("A1"), Unique:=True
lngRow = ws.Cells(Rows.Count, "A").End(xlUp).Row + 1
ws2.Columns(1).AdvancedFilter Action:=xlFilterCopy, _
CopyToRange:=ws.Range("A" & lngRow), Unique:=True
ws.Rows(lngRow).Delete
ws.Columns(1).AdvancedFilter Action:=xlFilterCopy, _
CopyToRange:=ws.Range("B1"), Unique:=True
ws.Columns(1).Delete
ws.Columns(1).Sort Key1:=Range("A2"), Order1:=xlAscending,
header:=xlYes
End Sub


If this post helps click Yes
---------------
Jacob Skaria


"Hardeep kanwar" wrote:

Hi Experts

I have data in Two Sheets,

For Example:
Sheet1

1
2
3
4
5
6
7
8
9
10
3
2
5
6


Sheet2

10
9
8
20
25
26
24
58
26
3
4
5
3
78
79
3
3
3


Expected Result Should be
1
2
3
4
5
6
7
8
9
10
20
25
26
24
58
78
79

Which Formula i use to get the Expected Result

Thanks in Advance

Hardeep Kanwar