Macro to remove Duploicates in Col A on sheet BR1 NV Units

[howard]

I have the following code below to remove duplicates in Col A on sheet BR1 unit sales


I get a run time error -Application defined or object defined error

It would be appreciated if someone could kindly amend my code

 Sub RemoveDuplicates()
  Sheets("BR1 NV Units").Range("A1").CurrentRegion.RemoveDuplicates 9, 1
  End Sub

 

[vcoolio]

Hello Howard,

You should only need to change the 9 to 1.

I hope that this helps.

Cheerio,
vcoolio.

 

[howard]

Many thanks for your help

When Running the macro the 1st duplicate is not being removed. I need tho retain the unique items in Col A

Dropbox

www.dropbox.com

kindly test and amend

 

[Jimmypop]

Hi there

Have you tried the below...

If the first duplicate is not being removed, it's possible that the data range being used does not actually include the entire column A. You may want to adjust the range to include all the data in column A.

To ensure that only unique items are retained in column A, you can modify the code as follows:

Sub RemoveDuplicates()
    Dim ws          As Worksheet
    Dim dataRange   As Range
    Dim lastRow     As Long
    
    Set ws = ThisWorkbook.Sheets("BR1 NV Units")
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
    Set dataRange = ws.Range("A1:A" & lastRow)
    
    On Error Resume Next
    dataRange.RemoveDuplicates Columns:=1, Header:=xlNo        'If your data range does include a header row, you can set the "Header" argument to "xlYes"
    If Err.Number <> 0 Then
        MsgBox "Error: " & Err.Description
    End If
    On Error GoTo 0
End Sub

In this modified code, the data range is set to cover all the data in column A, and the "Header" argument is set to "xlNo" to indicate that there is no header row.

If you still encounter issues with duplicates not being removed, it could be that there are some subtle differences in the data values (such as leading/trailing spaces or non-printable characters). You may need to clean up the data before removing duplicates to ensure that all values are considered identical.

 

[howard]

Thanks Jimmypop

Your code works 100%

 

[vcoolio]

Hello Howard,

Your code amended as follows should work for you:

Sub RemoveDuplicates()
         Sheets("BR1 NV Units").Range("A1").CurrentRegion.RemoveDuplicates 1, 2
End Sub

As you don't have headings, the 1 needed to be changed to 2 which is the enumeration for Header:=xlNo

Anyway, Jimmy has given you another option.

Cheerio,
vcoolio.

 

[howard]

Thanks vcoolio

your code works perfectly

 

[vcoolio]

Thanks for the feed back Howard. Glad I could help.

Cheerio,
vcoolio.