Excel macro "Run-time error '1004"

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

3 Answers

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 

This error occur if The Microsoft Visual Basic Applications copies and pastes whole row in an Excel 2003 workbook or this error may be occur if the Microsoft copies and pastes a range of 2,516 rows or more rows in an Excel 2003 workbook at this cases runtime error 1004 occurs. To get solution of this error save the workbook and manipulate the code of the macro in which you can save workbook.

ActiveSheet.Range("A1:C100").RemoveDuplicates Columns:=Array(1,2), Header:=xlYes

Here is info I found about your situation, I hope it is helpful.