Runtime 1004 when using Range.RemoveDuplicates

Question

First time post here, but found the site to be incredibly useful in the past.

I've written a macro to copy data from one worksheet to another, sort A->Z on two columns and then remove duplicate entries, before applying some formatting. It was working a couple of weeks ago, but has stopped working since I decided to replace .Select statements with defined worksheets and ranges (considered good practice from what I've read).

Currently I'm getting a run-time 1004 error (application-defined or object-defined error) on the following line:

desMdWs.Range("A6:D" & (Range("A" & Rows.Count).End(xlUp).Row)).RemoveDuplicates Columns:=Array(1, 2), Header:=xlYes

Full sub code below:

Sub UpdateMasterDataList(resWs, mdWs, estWs)
'
' UpdateMasterDataList Macro
' Updates the ATC Master Data tab with any new exceptions found
'
'
' Copy unique values from ATC results list to Remediation Master Data list
'
Dim srcWs As Worksheet
Dim srcRng As Range
Dim desMdWs As Worksheet
Dim desMdRng As Range
Dim desEstWs As Worksheet
Dim desEstRng As Range
Dim LastRow As Long
' Define worksheets to copy from and to
Set srcWs = resWs
Set desMdWs = mdWs
Set desEstWs = estWs
' Define cell ranges to copy from and to
Set srcRng = srcWs.Range("B2:C" & (Range("B" & Rows.Count).End(xlUp).Row))
Set desMdRng = desMdWs.Range("A" & (Range("A6").End(xlDown).Offset(1).Row))
Set desEstRng = desEstWs.Range("A8")

' Perform copy and paste
'Dim srcArray() As Variant
'srcArray = Range("srcRng")
'Dim i As Long
'For i = LBound(srcArray, 1) To UBound(srcArray, 1)
'    Debug.Print "srcRng = " & srcArray(i, 1)
'Next
'
'For Each strval In desMdRng
'    Debug.Print "desMdRng = " & desMdRng.Value
'Next
srcRng.Copy
desMdRng.PasteSpecial Paste:=xlPasteValues

'
' Sort the list A-Z
'
'desMdWs.Range ("A3:B" & (Range("B" & Rows.Count).End(xlUp).Row)) 'not needed
desMdWs.Sort.SortFields.Clear
desMdWs.Sort.SortFields.Add Key:= _
    Range("A6:A" & (Range("A" & Rows.Count).End(xlUp).Row)), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
    xlSortNormal
desMdWs.Sort.SortFields.Add Key:= _
    Range("B6:B" & (Range("B" & Rows.Count).End(xlUp).Row)), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
    xlSortNormal
With desMdWs.Sort
    .SetRange Range("A6:B" & (Range("B" & Rows.Count).End(xlUp).Row))
    .Header = xlYes
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
End With
'
' Remove duplicates from the list
'
desMdWs.Range("A6:D" & (Range("A" & Rows.Count).End(xlUp).Row)).RemoveDuplicates Columns:=Array(1, 2), Header:=xlYes
'
' Autofit the columns
'
desMdWs.Columns("A:A").EntireColumn.AutoFit
desMdWs.Columns("B:B").EntireColumn.AutoFit
'
' Add borders
'
Dim desMdTab As Range
Set desMdTab = desMdWs.Range("A6:D" & (Range("A" & Rows.Count).End(xlUp).Row))
desMdTab.Borders(xlDiagonalDown).LineStyle = xlNone
desMdTab.Borders(xlDiagonalUp).LineStyle = xlNone
With desMdTab.Borders(xlEdgeLeft)
    .LineStyle = xlContinuous
    .ColorIndex = 0
    .TintAndShade = 0
    .Weight = xlThin
End With
With desMdTab.Borders(xlEdgeTop)
    .LineStyle = xlContinuous
    .ColorIndex = 0
    .TintAndShade = 0
    .Weight = xlThin
End With
With desMdTab.Borders(xlEdgeBottom)
    .LineStyle = xlContinuous
    .ColorIndex = 0
    .TintAndShade = 0
    .Weight = xlThin
End With
With desMdTab.Borders(xlEdgeRight)
    .LineStyle = xlContinuous
    .ColorIndex = 0
    .TintAndShade = 0
    .Weight = xlThin
End With
With desMdTab.Borders(xlInsideVertical)
    .LineStyle = xlContinuous
    .ColorIndex = 0
    .TintAndShade = 0
    .Weight = xlThin
End With
With desMdTab.Borders(xlInsideHorizontal)
    .LineStyle = xlContinuous
    .ColorIndex = 0
    .TintAndShade = 0
    .Weight = xlThin
End With
desMdWs.Range("D7").AutoFill Destination:=desMdWs.Range("D" & (Range("D" & Rows.Count).End(xlUp).Offset(1).Row) & ":D" & (Range("A" & Rows.Count).End(xlUp).Row)), Type:=xlFillDefault
End Sub

If anyone can spot where I'm going wrong it would be greatly appreciated.

Cheers, James

Amandine FAURILLOU Amandine FAURILLOU · Accepted Answer · 2016-01-12T11:09:07

Your Range("A" & Rows.Count).End(xlUp).Row doesn't have its sheet specified, that is why VBA is not finding it.

Try

desMdWs.Range("A" & Rows.Count).End(xlDown).Row

with xlDown instead of up, which will give you the last non empty row. (from what I've gathered, xlDown is the equivalent of ctrl + down)

Runtime 1004 when using Range.RemoveDuplicates

1 Answers