Excel VBA code to remove duplicates

Click here to download the workbook which contains the code

Copy below code and edit below VBA code as per your requirement

Sub Rmv_dupli()
'www.comexcelhub.com
'This code is used to remove the duplicates

'Below Variable declaration for workbook and worksheet
Dim Twb As Workbook
Dim sht As Worksheet

'Below assign values to the workbook and worksheet Variables
Set Twb = ThisWorkbook
Set sht = Twb.Sheets("Sheet1")

'Below code is to copy duplicate content data
sht.Range("B1:B20").Copy

'Below code is to paste duplicate content data
sht.Range("D1").PasteSpecial Paste:=xlPasteValues
sht.Range("G1").PasteSpecial Paste:=xlPasteValues

'Below code is to remove the cut/copy mode in the excel application
Application.CutCopyMode = False

'Below code is to remove duplicates with headers
sht.Range("D1:D20").RemoveDuplicates Columns:=1, Header:=xlYes

'Below code is to remove duplicates without headers
sht.Range("G1:G20").RemoveDuplicates Columns:=1, Header:=xlNo

End Sub

Explanation of Excel VBA Code to remove duplicates each code line

Code Title:Sub Rmv_dupli()

Explanation: Title of the Macro is Rmv_dupli

Code Line 1:Dim Twb As Workbook
Code Line 2:Dim sht As Worksheet

Explanation: In line 1 & 2 variables Twb as workbook and Sht as worksheet are declared.

Code Line 3:Set Twb = ThisWorkbook
Code Line 4:Set sht = Twb.Sheets("Sheet1")

Explanation: In line 3 & 4 setting values to variables Twb as thisworkbook and Sht as Sheet1.

Code Line 4:sht.Range("B1:B20").Copy

Explanation: In Line 4 we are copying the duplicate content to paste in another location.

Code Line 5: sht.Range("D1").PasteSpecial Paste:=xlPasteValues
Code Line 6: sht.Range("G1").PasteSpecial Paste:=xlPasteValues

Explanation: In Line 5&6 we are pasting the copied duplicate content to in new another location.

Code Line 7:Application.CutCopyMode = False

Explanation: In Line 7 we are removing the Excel application cutcopymode so that it remove the blinking dotted lines around the copied range.

Code Line 8:sht.Range("D1:D20").RemoveDuplicates Columns:=1, Header:=xlYes

Explanation: In Line 8 we are removing duplicates with headers options as “yes”. It will ignore header from checking duplicates that is the reason in result we will have dates.

Code Line 9:sht.Range("G1:G20").RemoveDuplicates Columns:=1, Header:=xlNo

Explanation: In Line 9 we are removing duplicates with headers options as “no”. It will take header also normal data for checking duplicates that is the reason in result we will see only one time as dates.

Code Title:End Sub

Explanation Exit Line: This code is to end the Excel macro (End Subroutine).