its saying

Run time error 1004
method union of object _application failed

"Chip Pearson" wrote:

You can't use "And" to combine ranges. Instead, use "Union".

Replace
Set AllCells = Sheet1.Range("H2:H1000") And Sheet5.Range("D2:D1000")
with
Set AllCells =
Application.Union(Sheet1.Range("H2:H1000"),Sheet5. Range("D2:D1000"))


--
Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)





"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