VBA - Copy data to new range then sort and remove duplicate

[Chowz]

I want some example of VBA that could copy data (A1:B15) to new range (D1:E15) then sort and remove duplicate.


[TABLE="width: 672"]
<colgroup><col><col span="2"><col><col></colgroup><tbody>[TR]
[TD="align: left"][/TD]
[TD="align: left"][/TD]
[TD][/TD]
[TD][/TD]
[TD]
[TABLE="width: 700, align: left"]
<tbody>[TR]
[TD]SWS[/TD]
[TD]Central Pinklao FLG[/TD]
[TD] [/TD]
[TD]RMA Trading[/TD]
[TD]Thonglor 13[/TD]
[/TR]
[TR]
[TD]SWS[/TD]
[TD]IT Square[/TD]
[TD][/TD]
[TD]SWS[/TD]
[TD]Central Pinklao FLG[/TD]
[/TR]
[TR]
[TD]SWS[/TD]
[TD]IT Square[/TD]
[TD][/TD]
[TD]SWS[/TD]
[TD]IT Square[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]SWS[/TD]
[TD]Big C Wongsawang[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]SWS[/TD]
[TD]Ayutthaya Park[/TD]
[/TR]
[TR]
[TD]SWS[/TD]
[TD]Ayutthaya Park[/TD]
[TD][/TD]
[TD]SWS[/TD]
[TD]Lotus Srinagarindra[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]SWS[/TD]
[TD]The Mall 5 Thapra[/TD]
[/TR]
[TR]
[TD]SWS[/TD]
[TD]Big C Wongsawang[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]SWS[/TD]
[TD]Lotus Srinagarindra[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]SWS[/TD]
[TD]Lotus Srinagarindra[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]RMA Trading[/TD]
[TD]Thonglor 13[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]SWS[/TD]
[TD]The Mall 5 Thapra[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

Thank you for every helps.

 

[hiker95]

Chowz,

Sample raw data:

Excel 2007
	A	B	C	D	E
1	SWS	Central Pinklao FLG
2	SWS	IT Square
3	SWS	IT Square
4
5
6	SWS	Ayutthaya Park
7
8	SWS	Big C Wongsawang
9
10	SWS	Lotus Srinagarindra
11	SWS	Lotus Srinagarindra
12
13	RMA Trading	Thonglor 13
14
15	SWS	The Mall 5 Thapra
16
Sheet1

After the macro:

Excel 2007
	A	B	C	D	E
1	SWS	Central Pinklao FLG		RMA Trading	Thonglor 13
2	SWS	IT Square		SWS	Ayutthaya Park
3	SWS	IT Square		SWS	Big C Wongsawang
4				SWS	Central Pinklao FLG
5				SWS	IT Square
6	SWS	Ayutthaya Park		SWS	Lotus Srinagarindra
7				SWS	The Mall 5 Thapra
8	SWS	Big C Wongsawang
9
10	SWS	Lotus Srinagarindra
11	SWS	Lotus Srinagarindra
12
13	RMA Trading	Thonglor 13
14
15	SWS	The Mall 5 Thapra
16
Sheet1

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

1. Copy the below code
2. Open your NEW workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Sub RemoveDupesSort()
' hiker95, 07/04/2014, ME789286
Dim lr As Long
Application.ScreenUpdating = False
Columns("D:E").ClearContents
Rows(1).Insert
Cells(1, 1).Resize(, 2).Value = Array("A", "B")
Columns("A:B").AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Columns("D:E"), Unique:=True
Rows(1).Delete
lr = Cells(Rows.Count, "D").End(xlUp).Row
Range("D1:E" & lr).SpecialCells(xlCellTypeBlanks).Delete Shift:=xlUp
lr = Cells(Rows.Count, "D").End(xlUp).Row
Range("D1:E" & lr).Sort key1:=Range("D1"), order1:=1, key2:=Range("E1"), order1:=1
Columns("D:E").AutoFit
Application.ScreenUpdating = True
End Sub

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm

Then run the RemoveDupesSort macro.