Problem with filtering unique value of 800,000 rows using Advanced Filter

[Cakz Primz]

Dear MrExcel,

I need to extract, filtering the unique PO Number in sheet A, column X and then copy only the visible rows into sheet B, column D.
The number of rows is reaching 800,000 rows.
I am using Microsoft Office 365.

I've tried for to filtering using Advanced Filter, fail. It gave me, "not responding". Even though I am only opening this workbook only.
Can anyone would like to share a macro to solve this problem?

Thanks in advanced, really appreciate for your kind assistance and helping hand.

 

[Akuini]

Try using dictionary object instead of filter.
This will give you unique data.
Note:
1. you need to change sheet & range references to suit
2. it's case sensitive. If you want it to be case insensitive then uncomment this line: 'd.CompareMode = vbTextCompare

Sub Cakz_Primz()

Dim i As Long
Dim va, x
Dim d As Object

With Sheets("Sheet1")
    va = .Range("A2", .Cells(.Rows.Count, "A").End(xlUp))
End With

Set d = CreateObject("scripting.dictionary")
'd.CompareMode = vbTextCompare

For i = 1 To UBound(va, 1)
    d(va(i, 1)) = 1
Next

ReDim va(1 To d.Count, 1 To 1)

i = 0
For Each x In d.keys
   i = i + 1
   va(i, 1) = x
Next
'put the result
Sheets("Sheet2").Range("A2").Resize(UBound(va, 1), 1) = va
End Sub

Example:

Book1
	A
1	NAME
2	Rodrigo
3	Sullivan
4	Giovanni
5	Cayson
6	Blaise
7	Patrick
8	Damian
9	Todd
10	Kaleb
11	Cayson
12	Blaise
13	Patrick
14	Damian
Sheet1

Result:

Book1
	A
1
2	Rodrigo
3	Sullivan
4	Giovanni
5	Cayson
6	Blaise
7	Patrick
8	Damian
9	Todd
10	Kaleb
Sheet2

 

[Cakz Primz]

Dear Akuini,

I've tried your magical code, and it worked !!!
Very fast !!!
Problem solved.

Thanks so much for saving my life.

Dear MrExcel,
Thank you

 

[Akuini]

You're welcome, glad to help & thanks for the feedback.