Remove duplicates macro for a table ?

[MasterBash]

I am looking to remove duplicates from column B :




Date
Order
Shipping status
Animal


1
14-06-24
1000
Shipped
Dog


2
14-06-24
1001
In-progress
Cat


3
14-06-24
1001
Staged
Cat


4
14-06-24
1001
In-progress
Elephant



We can see that B2, B3 and B4 are duplicates. I would like to remove the entire rows for two of them using a vba script, then automatically sort column D alphabetically (Cat, Dog, Elephant).
I know I can do those two things individually with the Data -> Remove duplicate and then sort, but I would like to do it with just a click of a button.

Few things :
This must be done from the current (active) sheet, as we have multiple sheets with the same table. The table names are unknown, so using the table name is impossible. However, the column headers are static and in this case for this column it is "Order".

Thank you!

[Aussiebear]

I am looking to remove duplicates from column B :




Date
Order
Shipping status
Animal


1
14-06-24
1000
Shipped
Dog


2
14-06-24
1001
In-progress
Cat


3
14-06-24
1001
Staged
Cat


4
14-06-24
1001
In-progress
Elephant



We can see that B2, B3 and B4 are duplicates.

Sorry but your logic is a little wayward. Values B2,B3 & B4 are not duplicates. They refer to the same Order, but reflect either a different Status, or a different animal.



This must be done from the current (active) sheet, as we have multiple sheets with the same table. The table names are unknown, so using the table name is impossible. However, the column headers are static and in this case for this column it is "Order".

The tables will have a object number and a sheet name.

Can you please attach a sample workbook, so we can remove any ambiguity fro your request?





I would like to remove the entire rows for two of them using a vba script, then automatically sort column D alphabetically (Cat, Dog, Elephant).
I know I can do those two things individually with the Data -> Remove duplicate and then sort, but I would like to do it with just a click of a button.

Few things :
This must be done from the current (active) sheet, as we have multiple sheets with the same table. The table names are unknown, so using the table name is impossible. However, the column headers are static and in this case for this column it is "Order".

Thank you![/QUOTE]

[MasterBash]

31654

I added the Before and After removing duplicates (column B) and sorting (Column D, made a mistake on the sheet and sorted column B, my bad). However, that remove duplicates and sorting will be done in the same table (In the Before sheet).
It only matters if Order has duplicates, not the other columns.

Everyday, we add a new sheet based on that day's date and a new table is being created off a template. So every day, the new sheet will have a different name (date) and the table name will be different. However, the table's columns stay the same.

[Paul_Hossler]

Guessing here

I made some dummy data in the attachment




Option Explicit


Sub WhatYouAskedFor()
Dim r As Range, r1 As Range

Application.ScreenUpdating = False

Set r = ActiveSheet.Cells(1, 1).CurrentRegion
Set r1 = r.Cells(2, 1).Resize(r.Rows.Count - 1, r.Columns.Count)

r.RemoveDuplicates Columns:=2, Header:=xlYes

With ActiveSheet.Sort
.SortFields.Clear
.SortFields.Add Key:=r1.Columns(4), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
.SetRange r
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With

Application.ScreenUpdating = True

End Sub

[MasterBash]

That works great Paul, thank you very much !