chip said:
A Range cannot span multiple sheets.

so, perhaps you can link the data in sheet 5 to another, not used, not
seen, not printed area of sheet 1 - like waaaaaaay off in column FA or
something. then the ranges would be on the same sheet..........
just an idea
susan


On Jun 17, 10:10*am, Ewing25
wrote:
Ok well heres what im trying to do.

I have a column in sheet1 named Trips and a column in sheet5 named trips i
want to take the data in both calumns and have it displayed in a listbox when
the button is selected.

Do you have any idea how i can do that?



"Joel" wrote:
the set statement will never work because you can't combine ranges from two
different sheets. *Below is the correct format in you had two columns on the
same shet

Set AllCells = Union(Sheets("Sheet1").Range("H2:H1000"), _
* *Sheets("Sheet1").Range("D2:D1000"))

"Ewing25" wrote:

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- Hide quoted text -

- Show quoted text -