VBA's .removeDuplicates does not appear to work... Why?

Question

I have a column vector, let's say

1 2 1 2 1 1 1 1 1

and I need to programmatically remove duplicates. So after the removal of duplicates the output should be just:

1 2

However, VBA's .removeDuplicates method fails, triggering Error 1004 (application defined error).
My setup as follows: I have one workbook that contains the actual data. Second workbook contains the vba code that operates on the first workbook.

Observation #1:

When I execute the .removeDuplicates method from within the data-containing workbook and therefore data containing worksheet, the code works like a charm.

sub test  
 ActiveSheet.Range("$a$2:$a$20").RemoveDuplicates Columns:=1, Header:=xlYes
end sub

Observation #2:

However the moment that I try to run the very same code from a another workbook (with slight modification to address the fact the code is external to the first workbook), the .removeDuplicates fails:

Sub test()
    Dim wb As Workbook
    Dim sh As Worksheet

    'get a handle to data containing workbook and sheet
    Set wb = Workbooks(1) 'change wb index as needed
    Set sh = wb.Sheets("s2") ' change sheet name  as needed
    sh.Range("$a$2:$a$20").RemoveDuplicates Columns:=Array(1), Header:=xlYes
End Sub

Anyone had to deal with this before? Can I not use .removeDuplicates from another workbook? I look at the MSDN's API and there is no mention on limitations...

Answer 1

Remove duplicate behavior is bizarre, sometimes it works but sometimes not, basically it won't work while a cell is blank in selected range. to avoid remove duplicate run time error u could use following Procedure

Call remove_duplicate(ThisWorkbook.Name, "Mysheets", 14, 400) 

Above calling initiates following SUB and works same as remove remove duplicate Where

ThisWorkbook.Name is workbook name, for your case it is "1". Mysheets is sheet name so your case it will be "s2". 14 = Column Number, for your case it is 1. 400 = Last Row number, for your case it is 20.

Sub remove_duplicate(ByVal WorkBookName As String, ByVal worksht As String, ByVal col_Number As Integer, ByVal LastRow As Integer)
'WorkBook must be open withwise won't work
'col_Number = the column u want to test duplicate value

Dim i As Long
Dim CalcMode, ViewMode As Variant

Workbooks(WorkBookName).Worksheets(worksht).Activate

If LastRow <= 1 Then  'if Rows on column is empty or only contains header then exit the sub
    Exit Sub
End If

With Application
    CalcMode = .Calculation
    .Calculation = xlCalculationManual
    .ScreenUpdating = False
End With

ColChr = Split(Cells(1, col_Number).Address, "$")(1)
With ActiveSheet
    If ActiveSheet.AutoFilterMode = True Then ActiveSheet.AutoFilterMode = False
    ViewMode = ActiveWindow.View
    ActiveWindow.View = xlNormalView
    Range("A1").Select
    For i = LastRow To 2 Step -1
        CheckVal = Range(ColChr & i).Value
        CountVal = Application.CountIf(Range(ColChr & ":" & ColChr), CheckVal)
        If CountVal > 1 Then
            Rows(i).EntireRow.Delete
        End If
    Next i
End With

ActiveWindow.View = ViewMode
With Application
   .ScreenUpdating = True
   .Calculation = CalcMode
   .ScreenUpdating = True
End With

End Sub