Stuck with Macro to Remove Duplicate Rows

Philip1957

Board Regular
Joined
Sep 30, 2014
Messages
182
Office Version
  1. 365
Platform
  1. Windows
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?

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

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).
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.
 
Last edited:
Upvote 0
Here is an alternative solution. One that I have used for many years.

Code:
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.
 
Last edited:
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,557
Messages
6,114,287
Members
448,562
Latest member
Flashbond

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
Back
Top