VBA Remove Duplicates Error

Question

So, I am trying to delete any rows that have duplicate data in column A. I thought this was pretty elegant compared to some of the loops I saw people asking about. However, I get an error on the line rng.RemoveDuplicates Columns:=1, Header:=xlYes. The error is Run-time error 1004. Application defined or object defined error.

Ideally, we can get the error to go away while also removing duplicates rows. Any help would be appreciated! Thanks!

Private Sub RemoveDuplicate_Click()

  Dim LastRow As Long
  Dim rng As Range
  Dim ws As Worksheet
  Set ws = Worksheets("Scorecard")

  LastRow = ws.Range("A" & Rows.Count).End(xlUp).Row
  Set rng = ws.Range("A1:K" & LastRow)
  rng.RemoveDuplicates Columns:=1, Header:=xlYes
End Sub

Also, I tried this code as well and got the same error.

Private Sub RemoveDuplicate_Click()

 Dim LastRow As Long
 Dim rng As Range
 Worksheets("Scorecard").Activate
 LastRow = ActiveSheet.Range("A" & Rows.Count).End(xlUp).Row
 Set rng = ActiveSheet.Range("A1:K" & LastRow)
 rng.RemoveDuplicates Columns:=1, Header:=xlYes

End Sub

Tim Williams · Accepted Answer · 2018-06-20 19:49:44Z

1

rng.RemoveDuplicates Columns:=Array(1),  Header:=xlYes

https://msdn.microsoft.com/en-us/vba/excel-vba/articles/range-removeduplicates-method-excel

Columns: Array of indexes of the columns that contain the duplicate information.

answered Jun 20, 2018 at 19:49

Tim Williams

169k8 gold badges104 silver badges143 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

FlexOff Over a year ago

I replaced my line of code with the one you said and still got the same error. I tried it with both of my examples above.The error is Run-time error 1004. Application defined or object defined error. Also occurring at the same spot.

Tim Williams Over a year ago

What's the value of LastRow when it fails?

Tim Williams Over a year ago

Your code works fine for me (even without the Array() edit)

FlexOff Over a year ago

I added a msgbox to print LastRow. It returns 138

FlexOff Over a year ago

There was a blank row. A2 was a blank row. I changed the code from set rng = ActiveSheet.Range("A1:K" & LastRow) to set rng = ActiveSheet.Range("A2:K" & LastRow) and it worked. Thanks Tim!

Collectives™ on Stack Overflow

VBA Remove Duplicates Error

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related