Extract All lines cotaining the last distinct value.

[Alegzandrion]

Hi, I am currently relatively new to excel and I have an issue. I want to extract from a sheet all the lines that contain the last distinct value of a column.

So have something like this
534 16 14
534 64 16
020 24 22
020 16 14
020 64 16
134 24 0
134 8 12
134 8 7
134 15 8
134 15 9

And I would Like to make a macro that would extract for me based on the first column only the bolded lines. After extraction the table should look like this :
534 64 16
020 64 16
134 15 9

Can anyone give me a helping hand ? I tried using lookup, but that only seems to work by entering a specific value.

 

[DataBlake]

Welcome to Mr. Excel,

I assume that the example you posted in three columns correct? you can use XL2BB in any future posts to paste your exact data.
So you are wanting to extract the last unique value in column A, but the entire row? and what do you mean by "extract"? (a new sheet, replace the current table, etc)

 

[Akuini]

Try this:
I put the result in col E.

Sub a1123065a()

Dim i As Long, j As Long, k As Long
Dim va
Application.ScreenUpdating = False
va = Range("A1", Cells(Rows.Count, "A").End(xlUp))
For i = 1 To UBound(va, 1)
    j = i
    Do
        i = i + 1
        If i > UBound(va, 1) Then Exit Do
    Loop While va(i, 1) = va(i - 1, 1)
    i = i - 1:    k = k + 1
    Range("E" & k).Resize(1, 3).Value = Range("A" & i).Resize(1, 3).Value
Next
Application.ScreenUpdating = True
End Sub

Example:

Book1
	A	B	C	D	E	F	G
1	534	16	14		534	64	16
2	534	64	16		20	64	16
3	20	24	22		134	15	9
4	20	16	14
5	20	64	16
6	134	24	0
7	134	8	12
8	134	8	7
9	134	15	8
10	134	15	9
Sheet11

 

[Alegzandrion]

Welcome to Mr. Excel,

I assume that the example you posted in three columns correct? you can use XL2BB in any future posts to paste your exact data.
So you are wanting to extract the last unique valuge in column A, but the entire row? and what do you mean by "extract"? (a new sheet, replace the current table, etc)

I did not paste the entire sheet as it is very big. I want to create in another sheet a table based on all the lines of another table, by extracting every ROW containing the last occurence of every distinct value of a column.