Deleting Rows with Duplicate Data in a User defined Column

[Philip1957]

Greetings,

Some days I amaze myself, other days I have trouble putting my socks on.

This should be an easy macro to create, but I'm just not having any success.

I want a macro for my (or someone else's) Personal.xlsb that will remove rows based on duplicate data in a column specified by the user.

I've got this:

Option Explicit

Sub Rmv_Dupe_Rows()

Application.ScreenUpdating = False

'On Error Resume Next

Dim clmn As String

clmn = Application.InputBox("Enter the letter designating" & vbCrLf & "the column where the duplicate" & vbCrLf & "data is located.")

ActiveSheet.UsedRange.RemoveDuplicates Columns:=clmn, Header:=xlGuess

Application.ScreenUpdating = True

End Sub

clmn gets saved, I can see it in the Locals window.

I get a Run-time error '13': Type mismatch at the line

ActiveSheet.UsedRange.RemoveDuplicates Columns:=clmn, Header:=xlGuess

Any assistance would be greatly appreciated.

Thanks in advance for your time,
~ Phil

 

[Fluff]

The clmn value needs to ba a number not a string.
Are you happy getting the user to enter the column number rather than the column letter?

 

[Joe4]

The function uses column number, not column letter.
You can make some minor modifications to your code to convert it so that it will work with your current structure:

Sub Rmv_Dupe_Rows()

    Application.ScreenUpdating = False

'   On Error Resume Next

    Dim clmn As String
    Dim col As Long

'   Prompt for column letter
    clmn = Application.InputBox("Enter the letter designating" & vbCrLf & "the column where the duplicate" & vbCrLf & "data is located.")
'   Convert to column number
    col = Cells(1, clmn).Column
    
    ActiveSheet.UsedRange.RemoveDuplicates Columns:=col, Header:=xlGuess
    
    Application.ScreenUpdating = True

End Sub

 

[Philip1957]

Thanks for the response!

I don't know that the end users will necessarily understand that they need to count the columns rather than use the letter.

Many of them don't even know how to use VLOOKUP.

I've gotten a solution from another contributor.

Thanks anyway.

 

[Philip1957]

Joe4,

I didn't realize the function required a numeric input for the column.

Like I said, sometimes I amaze myself, other times ... not so much.

Thank you for your speedy response!

Your modification to my code works perfectly.

Thanks again,
~ Phil

 

[Joe4]

You are welcome!
Glad we could help.