In Excel VBA, Can I do a find on a range for all cells containing a value and have a range with just those cells found returned?

In Excel VBA, Can I do a find on a range for all cells containing a value and have a range with just those cells found returned?

This would more than likely be a non-contiguous range.  Is this possible?  If so, how?

Let's say I wanted to find all cells in a range with the text "Joe" in them and then get a range of ONLY those cells that had that text in them.  What would the code look like for this?
cashonlyAsked:
Who is Participating?
 
[ fanpages ]Connect With a Mentor IT Services ConsultantCommented:
Hi,

Here is one method...

Option Explicit
Public Sub Q_28142220()

' --------------------------------------------------------------------------------------------------------------
' [ http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Q_28142220.html ]
'
' Question Channel: Experts Exchange > Software > Office / Productivity > Office Suites > MS Office
'
' ID:               28142220
' Question Title:   In Excel VBA, Can I do a find on a range for all cells containing a value and have a range
'                   with just those cells found returned?
' Question Asker:   cashonly                                  [ http://www.experts-exchange.com/M_1081164.html ]
' Question Dated:   2013-05-29 at 20:51:53
'
' Expert Comment:   fanpages                                   [ http://www.experts-exchange.com/M_258171.html ]
' Copyright:        (c) 2013 Clearlogic Concepts (UK) Limited                           [ http://NigelLee.info ]
' --------------------------------------------------------------------------------------------------------------

  Dim objCell                                           As Range
  Dim objStart                                          As Range
  Dim objRange                                          As Range
  
  Set objCell = ActiveSheet.UsedRange.Find("Joe")
  Set objStart = objCell
  
  While Not (objCell Is Nothing)
  
      DoEvents
      
      If (objRange Is Nothing) Then
         Set objRange = objCell
      Else
         Set objRange = Union(objRange, objCell)
      End If ' If (objRange Is Nothing) Then
      
      Set objCell = ActiveSheet.UsedRange.Find(What:="Joe", _
                                               After:=objCell)
  
      If objCell.Address = objStart.Address Then
         Set objCell = Nothing
      End If ' If objCell.Address = objStart.Address Then
      
  Wend
  
  If Not (objRange Is Nothing) Then
     objRange.Select
     Set objRange = Nothing
  End If ' If Not (objRange Is Nothing) Then
  
  Set objStart = Nothing
  Set objCell = Nothing

End Sub

Open in new window


For convenience, please see the attached workbook.

BFN,

fp.
Q-28142220.xls
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.