Delete rows based on a specific Column

[jxj_00]

Hi

I have a database that needs to be regularly updated and to prevent any duplication of records the codes need to do the following:
1. To delete the entire row if any duplicates in Column A (Employee ID)
2. To duplicate the first entry (Meaning if there was a duplicate of row 1 with row 15 it will delete row 1)

I have attach a copy of the excel in this link: Box

Sub RemoveDuplicateRows()

Dim MyRange As Range
Dim LastRow As Long

LastRow = ActiveSheet.Range("A" & Rows.Count).End(xlUp).Row
Set MyRange = ActiveSheet.Range("A1:D" & LastRow)
MyRange.RemoveDuplicates Columns:=1, Header:=xlYes

End Sub

 

[Trixterz]

Your sheet is probably locked or protected. Try manually deleting that row 1004 and see if you get prompted...

 

[Fluff]

Once again check that you don't have any merged cells & that the sheet is not protected.

 

[jxj_00]

I see, let me take note of it when I run into an error again. Appreciate your help and patience!

 

[Fluff]

Ok

 

[jxj_00]

I inserted a code to unmerge all cells & turn off the display alert before deleting the duplicates. The code is running smoothly without any errors. Appreciate the advice given.

Sub jxj()
   Dim i As Long
   Dim Rng As Range
   Dim desWS As Worksheet
   Set desWS = Workbooks("WB1.xlsm").Sheets("WS1")
   Application.ScreenUpdating = False
   Application.DisplayAlerts = False 
   desWS.Cells.UnMerge
    Application.DisplayAlerts = True
   
   desWS.Activate
   With CreateObject("scripting.dictionary")
      For i = Range("A" & Rows.Count).End(xlUp).Row To 2 Step -1
         If Not .Exists(Cells(i, 1).Value) Then
            .Add Cells(i, 1).Value, Nothing
         Else
            If Rng Is Nothing Then Set Rng = Cells(i, 1) Else Set Rng = Union(Rng, Cells(i, 1))
         End If
      Next i
   End With
   If Not Rng Is Nothing Then Rng.EntireRow.Delete
   Application.ScreenUpdating = True
End Sub

 

[Fluff]

Glad you sorted it & thanks for the feedback.

 

[jxj_00]

Glad you sorted it & thanks for the feedback.

Hi, sorry to trouble you again. Would you mind explaining the code as I am trying to repurpose this code for something else? Thank you in advance.

 

[jxj_00]

Hi, sorry to trouble you again. Would you mind explaining the code as I am trying to repurpose this code for something else? Thank you in advance.

I had a bit of confusion with the union part of the code but I got it sorted. Appreciate the help given.