type mismatch error
You can't do this:
Sheet1.Range("H2:H1000") And Sheet5.Range("D2:D1000")
"Union" is usually the way to do it, but not across sheets. Anyway you don't
need to union them, try
For Each Cell In Sheet1.Range("H2:H1000")
NoDupes.Add Cell.Value, CStr(Cell.Value)
Next Cell
For Each Cell In Sheet5.Range("D2:D1000")
NoDupes.Add Cell.Value, CStr(Cell.Value)
Next Cell
HTH. Best wishes Harald
"Ewing25" wrote in message
...
Im getting a type mismatch error on this code and im not sure why.
Im getting the error on the "Set AllCells" line.
Sub RemoveDuplicates()
Dim AllCells As Range, Cell As Range
Dim NoDupes As New Collection
Set AllCells = Sheet1.Range("H2:H1000") And Sheet5.Range("D2:D1000")
On Error Resume Next
For Each Cell In AllCells
NoDupes.Add Cell.Value, CStr(Cell.Value)
Next Cell
On Error GoTo 0
For Each Value In NoDupes
On Error Resume Next
UserForm3.ListBox1.AddItem Value
Next Value
UserForm3.Show
End Sub
Thanks!
Alex
|