#Delete duplicate rows in excel vba code#

If you want to eliminate them, use the following code: Sub removeDuplicates() In this particular case, there are multiple headers that repeat themselves with no particular pattern. Range('A1:D10').RemoveDuplicates Columns:=Array(1), Header:= xlNo Without Headers Sub sbRemoveDuplicatesSpecificWithNoHeaders() Range('A1:D10').RemoveDuplicates Columns:=Array(1), Header:= xlYes With Headers Sub sbRemoveDuplicatesSpecificWithHeaders() If you must use VBA to achieve this, then you can use the RemoveDuplicates function:

For example, in this worksheet, the Name column has duplicates that I want to delete.Īnd after deleting duplicates from Name Column, that's how it looks:.

Click Data > Remove Duplicates, and then Under Columns, check or uncheck the columns where you want to remove the duplicates.

Select the range of cells that has duplicate values you want to remove.

#Delete duplicate rows in excel vba code#

There's no need to write VBA code to do this, just use the Excel function Remove Duplicates. This didnt work for me, it didnt delete the rows in which duplicate Headers have been found I tried to work with find and lastrowĭup = ws.Range('A2:C' & Lastrow).Find('Year', LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=False).Columns I dont know in which row the duplicate headers will be, also i dont know when the row ends.

In this example, I want to keep everything except the duplicate Headers (Row5 & 7 here), these should be deleted so in the end it will look like this:ī1:Department B2:Sales B3:Marketing B4:HR B5:Marketing