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.

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


"rockhammer" wrote in message
...
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.

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

--
Regards,
Tom Ogilvy

"rockhammer" wrote in message
...
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.

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.


"Peter T" wrote:

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


"rockhammer" wrote in message
...
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.

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


"Tom Ogilvy" wrote:

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

--
Regards,
Tom Ogilvy

"rockhammer" wrote in message
...
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.