Delete rows based on a specific Column

jxj_00

New Member
Joined
Oct 1, 2020
Messages
23
Office Version
  1. 365
Platform
  1. Windows
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

VBA Code:
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
 

Some videos you may like

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,585
Office Version
  1. 365
Platform
  1. Windows
How about
VBA Code:
Sub jxj()
   Dim i As Long
   Dim Rng As Range
   
   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
End Sub
 

jxj_00

New Member
Joined
Oct 1, 2020
Messages
23
Office Version
  1. 365
Platform
  1. Windows
How about
VBA Code:
Sub jxj()
   Dim i As Long
   Dim Rng As Range
  
   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
End Sub

The code works! Thank you for your help!
 

jxj_00

New Member
Joined
Oct 1, 2020
Messages
23
Office Version
  1. 365
Platform
  1. Windows
How about
VBA Code:
Sub jxj()
   Dim i As Long
   Dim Rng As Range
  
   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
End Sub

The 1st few times the code works but suddenly it showed an error message. Any idea on how to solve it?
It highlighted the If Not Rng Is Nothing Then Rng.EntireRow.Delete

1602060979914.png
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,585
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Is the sheet protected & do you have any merged cells?
 

jxj_00

New Member
Joined
Oct 1, 2020
Messages
23
Office Version
  1. 365
Platform
  1. Windows
Is the sheet protected & do you have any merged cells?

I think I had accidentally left some cells merged. I have unmerged them and the code has started running again, thanks for your help!
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,585
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

You're welcome & thanks for the feedback.
 

jxj_00

New Member
Joined
Oct 1, 2020
Messages
23
Office Version
  1. 365
Platform
  1. Windows
Hi

The code sometimes work sometimes doesn't work.

It highlights this portion of the code. May I know if there is an alternative code for this?

VBA Code:
  If Not Rng Is Nothing Then Rng.EntireRow.Delete
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,585
Office Version
  1. 365
Platform
  1. Windows
What error do you get?
 

jxj_00

New Member
Joined
Oct 1, 2020
Messages
23
Office Version
  1. 365
Platform
  1. Windows
I got an error for 1004 Delete method of Range class failed
 

Watch MrExcel Video

Forum statistics

Threads
1,127,681
Messages
5,626,264
Members
416,169
Latest member
Alwin David

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top