selecting a collection of cells from .Find()

[rockhammer]

I have two questions based on the following code which gives me the
collection "targetCells" where targetCells.Count can be in tens of thousands:

' start of code
Dim iSheet As Worksheet
Dim iRange As Range
Dim foundOne As Boolean
Dim firstCell As String
Set targetCells = New Collection
findValue = "xyz"
foundOne = False
For Each iSheet In ActiveWorkbook.Worksheets
Set iRange = iSheet.UsedRange
Set iCell = iRange.Find(findValue, LookIn:=xlFormulas, LookAt:=xlPart)
If Not iCell Is Nothing Then
firstCell = iCell.Address
Do
targetCells.Add iCell
foundOne = True
Set iCell = iRange.FindNext(iCell)
Loop While Not iCell Is Nothing And iCell.Address <> firstCell
End If
Next
' end of code

Question #1:
How can I convert the collection "targetCells" from the following code to
XXX such that I can do XXX.Select, i.e. select these cells I've found?

Question #2:
Is there a way to modify the code above such that I can get to XXX directly
without having to arrive at targetCells first?

Thanks a lot.

 

[Peter T]

You don't need the collection in making the range of all found cells unless
you need it for other purposes.

where targetCells.Count can be in tens of thousands:

If by that you mean you expect to find tens of thousands of cells and select
all, the normal simple method (below) will take a very long time if the
found cells will comprise a large number of multiple areas, ie non adjacent
cells.

Dim rMultiFound As Range
'code
'insert following in your Do loop

If rMultiFound Is Nothing Then
Set rMultiFound = iCell
Else
Set rMultiFound = Union(rMultiFound, iCell)
End If

This isn't suitable for extremely large multi areas unless you're very
patient, to do it in a time frame user a user is accustomed to expect is not
trivial. Even with other methods I wouldn't want to make a 'selection' of
8000+ areas as it becomes unstable, and would abort before making it (which
also requires working out the number of areas before making it).

Regards,
Peter T

 

[Tom Ogilvy]

In addition, you have cells on multiple sheets, so you will never be able to
do xxx.select

 

[rockhammer]

Thank you, Peter T. Your suggestion worked wonderfully.
I will watch the size of the selection, and will think of a way to make
smaller selections when i hit the performance issues you noted.

 

[rockhammer]

Thanks, Tom. I will modify the code to do one sheet at a time.