I use the Copy() method on non-contigous cells and ranges all the time.

Do both these work for you -
Range("A1:A2,C1:C2").Copy
Range("A1:A2,B3:B4").Copy

or only the first?

If your union only builds a few hundred areas ignore what I mentioned about
this aspect.

Regards,
Peter T


"Sisilla" wrote in message
ps.com...
I use the Copy() method on non-contigous cells and ranges all the time.
I guess I don't know what you mean by "multiarea" and "non-contiguous
area." I have never heard these terms in an Excel context. However, you
raise a good point with the address limit. In any case, Dave's
suggestions have considerably speeded up my run times, but thank you
for your input.

Peter T wrote:
If you are going to concatenate strings, with intermediates separated
with
commas,
don't do more than say 12 at a time before converting to a range or you
might exceed the 255 address limit.

As I mentioned union is only slow if you are likely to end up will large
multiarea ranges (note I don't mean multicell). Which way to cater
really
would depend on your ultimate objective with the range object(s). You
say in
the Copy method' but you can't use the copy method with non-contiguous
areas
anyway, so I'm not quite sure what you are doing.

Regards,
Peter T


"Sisilla" wrote in message
ups.com...
Hello Peter,

Thanks for your reply. Are you saying that it would be faster to
concatenate strings with every iteration of the loop than to union
non-contiguous ranges?

If this is indeed what you are saying, then I will have to find some
way to convert the returned string back to a range once it is needed
(in the Copy method). Thank you for your advice.

Sisilla

Peter T wrote:
You have answers to your main question but just to add, if your
'AtLeastCells' cells will exist in many non-contiguous areas your
union
loop
will become exponentially slower, eventually to a crawl. If that's a
possibility consider not making a single large multi area range
object
and
processing in a different way. Eg make an array of string addresses
for
later use, or process intermediate range objects that exceed say 100
areas.

Regards,
Peter T


"Sisilla" wrote in message
ps.com...
Hello All,

The following code runs slowly. Is there a better way to do this,
perhaps with CountIf and Find? If there is even the smallest
improvement from comparing every cell in the SearchRange with
CompareValue, I'd love to hear the solution!

Function AtLeastCells(CompareValue As Integer, SearchRange As
Range)
As
Range

'Searches SearchRange for values that are greater than or equal to
CompareValue
'of Integer Data Type.
'If values are found, all matching cells are returned.
'If no value is found, an empty range is returned.

Dim rCell As Range

For Each rCell In SearchRange.Cells
If rCell.Value = CompareValue Then
Set AtLeastCells = UnionWithNothing(rCell,
AtLeastCells)
End If
Next rCell

End Function

I greatly appreciate any help.

Thanks!
Sisilla