Object Required Error

[rguy84]

I am getting an error on the code below, and not sure why. Can somebody shed some light? (The macro isn't complete btw)

Sub SortAndRemoveDupes()
    Dim lastCell, lastContact As Long
    Dim rListSort, rOldList As Range
    Dim strRowSource As String
 
    Set lastCell = Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Row
    [COLOR=red]Set lastContact = Sheets("Contact").Range("H" & Row.Count).End(xlUp).Row[/COLOR]
 
    Sheets("Sheet2").Range("A1:E" & lastCell).Clear
 
    Set rOldList = Sheets("Contact").Range("D2:H" & lastContact)
 
    'copy unique list to sheet2
    rOldList.AdvancedFilter Action:=xlFilterCopy, _
               CopyToRange:=Sheet2.Cells(1, 1), Unique:=True
    'Set range variable to the new non dupe list
    Set rListSort = Sheet2.Range("A1", Sheet1.Range("E").End(xlUp))
End Sub

 

[VoG]

You need to Dim your variables correctly and remove Set from the lines that get row numbers

Sub SortAndRemoveDupes()
    Dim lastCell As Long, lastContact As Long
    Dim rListSort As Range, rOldList As Range
    Dim strRowSource As String
 
    lastCell = Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Row
    lastContact = Sheets("Contact").Range("H" & Row.Count).End(xlUp).Row
 
    Sheets("Sheet2").Range("A1:E" & lastCell).Clear
 
    Set rOldList = Sheets("Contact").Range("D2:H" & lastContact)
 
    'copy unique list to sheet2
    rOldList.AdvancedFilter Action:=xlFilterCopy, _
               CopyToRange:=Sheet2.Cells(1, 1), Unique:=True
    'Set range variable to the new non dupe list
    Set rListSort = Sheet2.Range("A1", Sheet1.Range("E").End(xlUp))
End Sub

 

[taurean]

You have declared it as long so the statement shall be:

[COLOR=black]lastContact = Sheets("Contact").Range("H" & Row[COLOR=red]s[/COLOR].Count).End(xlUp).Row[/COLOR]

And it should be Rows.Count as you have used correctly in the previous statement.

When you use "Set", VBA expects an object reference to be set which is not applicable in current case.

 

[rguy84]

Peter, I used set because after removing them, I get error 424 on lastContact = ...

I also thought I could do Dim var1, var2 as Range and it applies to both

 

[VoG]

You only use Set with Object variables.

This should fix it

lastContact = Sheets("Contact").Range("H" & Rows.Count).End(xlUp).Row

 

[rguy84]

It seems like Excel 2010 likes Sheets("blah") vs sheet1

 

[VoG]

You can use Sheet1 provided that the sheet's code name is actually Sheet1.

 

[rguy84]

You only use Set with Object variables.

This should fix it

lastContact = Sheets("Contact").Range("H" & Rows.Count).End(xlUp).Row

Yep that did.