Stuck with Macro to Remove Duplicate Rows

[Philip1957]

Greetings,

I have a macro to remove duplicate rows that I'm pretty sure used to run, but it isn't working now. I haven't used it in quite a while, but yesterday I needed it and it crashes with a run-time error 1004 at the "ActiveSheet" line.

Can anyone tell me what is wrong with it?

Sub Rmv_Dupe_Rows()

    Application.ScreenUpdating = False

    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

Thanks in advance for your time & assistance.

 

[Joe4]

I just tried it on some sample data that I made up and it worked fine. So I suspect it has something to do with your data.
Is there anything special about this sheet or data, i.e. are there any merged cells or protected cells?

Note: On further testing, I got that error when I tried entering a column letter where that column has no data in it.

 

[alansidman]

Here is an alternative solution. One that I have used for many years.

Sub DeleteDuplicateRows()
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' DeleteDuplicateRows
' This will delete duplicate records, based on the Active Column. That is,
' if the same value is found more than once in the Active Column, all but
' the first (lowest row number) will be deleted.
'
' To run the macro, select the entire column you wish to scan for
' duplicates, and run this procedure.
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''


    Dim r As Long
    Dim n As Long
    Dim v As Variant
    Dim rng As Range


    On Error GoTo EndMacro
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual




    Set rng = Application.Intersect(ActiveSheet.UsedRange, _
                                    ActiveSheet.Columns(ActiveCell.Column))


    Application.StatusBar = "Processing Row: " & Format(rng.Row, "#,##0")


    n = 0
    For r = rng.Rows.Count To 2 Step -1
        If r Mod 500 = 0 Then
            Application.StatusBar = "Processing Row: " & Format(r, "#,##0")
        End If


        v = rng.Cells(r, 1).Value
        '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
        ' Note that COUNTIF works oddly with a Variant that is equal to vbNullString.
        ' Rather than pass in the variant, you need to pass in vbNullString explicitly.
        '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
        If v = vbNullString Then
            If Application.WorksheetFunction.CountIf(rng.Columns(1), vbNullString) > 1 Then
                rng.Rows(r).EntireRow.Delete
                'rng.Rows(r).EntireRow.Copy Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Row + 1
                n = n + 1
            End If
        Else
            If Application.WorksheetFunction.CountIf(rng.Columns(1), v) > 1 Then
                rng.Rows(r).EntireRow.Delete
                'rng.Rows(r).EntireRow.Copy Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Row + 1
                n = n + 1
            End If
        End If
    Next r


EndMacro:


    Application.StatusBar = False
    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic
    MsgBox "Duplicate Rows Deleted: " & CStr(n)




End Sub

Your code works for me when tested on a valid column.

 

[Philip1957]

OK, this is weird.

It works on my primary monitor, but it won't run at all on my secondary monitor. Yesterday I was working with it on my secondary screen.

I tried a couple of my other macros and they don't execute on my secondary screen either.

I've never heard of anything like this. It must have something to do with: A) Microsoft made some changes; B) The Intel graphics manager on my laptop is screwy, or; C) It's because of something our corporate IT did.

I'll just run my macros from the primary monotir from now on.

Thanks for your time.
~ Phil