"Remove Duplicates" macro gives error on protected worksheet

[Closed]
Report
Posts
13
Registration date
Monday March 11, 2013
Status
Member
Last seen
August 14, 2013
-
Posts
2368
Registration date
Sunday September 23, 2012
Status
Member
Last seen
December 13, 2018
-
Hello,





I have a macro that sorts and removes duplicates from a fixed selection in a single column.
The cells A1:O1 (headers) and O2:O15 (calculated cells) are blocked. The rest of the sheet is unblocked.
My macro runs smoothly when the sheet is unprotected, but when I protect the sheet, so vital cell-content is secure, the macro runs into a 1004 error at the following line from my code:
ActiveSheet.Range("$A$2:$A$300").RemoveDuplicates Columns:=1, Header:=xlNo


Here is the complete code.
My question is: Can this macro do what I want in protected mode or not?
If yes, what should I change?

Sub Ontdubbelen()
'
    Range("A2:A300").Select
    ActiveWorkbook.Worksheets("PIM-Batch").Sort.SortFields.Add Key:=Range("A2"), _
        SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("PIM-Batch").Sort
        .SetRange Range("A2:A300")
        .Header = xlNo
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    ActiveSheet.Range("$A$2:$A$300").RemoveDuplicates Columns:=1, Header:=xlNo
    Selection.NumberFormat = "0"
    With Selection
        .HorizontalAlignment = xlGeneral
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    With Selection.Font
        .Name = "Arial"
        .Size = 10
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
        .TintAndShade = 0
        .ThemeFont = xlThemeFontNone
    End With
End Sub

1 reply

Posts
2368
Registration date
Sunday September 23, 2012
Status
Member
Last seen
December 13, 2018
43
Hi Pluggie,

You are receiving this error message because your sheet is password protected. I have added the part of code that will unlock the sheet and then lock it again after executing the macro. Please check the below mentioned link to download the sample sheet:

http://speedy.sh/5FPW8/MacroProtect.xlsm

The password for the sheet is: ZOHAIB

Please revert for clarification.