Excel macro “Run-time error '1004”

Question

I am new to scripting and I am trying to improve a existing Macro. I recorded a macro to remove dupliate and added it in a Main function which calls some other functions, but I am getting this error when I add the macro I recorded: Run-time error '1004': Application-defined or object-defined error.

The code looks like

Sub Main()
Call DuplicateRemove
Call DeleteBlankRows
Call TrimText
End 

Sub DeleteBlankRows()
.
.
End Sub

Sub TrimText()
.
.
End Sub

Sub DuplicateRemove()
Columns("A:A").Select
ActiveSheet.Range("$A$1:$A$95678").RemoveDuplicates Columns:=1, Header:=xlNo
End Sub

Thanks, Kiran

I am getting the error only when I add the macro I recorded: Sub DuplicateRemove() Columns("A:A").Select ActiveSheet.Range("$A$1:$A$95678").RemoveDuplicates Columns:=1, Header:=xlNo End Sub — Kiranshell
Does Sub TrimText() . . imply that you actually have the words End Sub somewhere in there? — Brad
"'1004': Application-defined or object-defined error." effectively means that you have an unhandled error in your VBA code. Add error-handling with MSGBOX statements to see what the actual VBA error is... — RBarryYoung

Siddharth Rout Siddharth Rout · Accepted Answer · 2012-07-31T21:41:12

There is nothing wrong with your code. You will only get this error if the Active worksheet is password protected.

Also it is a much better option to avoid using .Select and ActiveSheet. Your code can be written as

Sub DuplicateRemove()
    Dim ws As Worksheet

    Set ws = Sheets("Sheet1")

    With ws
        If .ProtectContents = True Then
            MsgBox "Worksheet is protected"
            .Unprotect "MYPASSWORD"
            .Range("$A$1:$A$95678").RemoveDuplicates Columns:=1, Header:=xlNo
            .Protect "MYPASSWORD"
        Else
            .Range("$A$1:$A$95678").RemoveDuplicates Columns:=1, Header:=xlNo
        End If
    End With
End Sub

FOLLOWUP

Sub DuplicateTest()
    ActiveSheet.Columns(1).RemoveDuplicates Columns:=1, Header:=xlNo
End Sub

Excel macro “Run-time error '1004”

3 Answers