Solved

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?

Posted on 2013-05-29
1
406 Views
Last Modified: 2013-06-06
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?
0
Comment
Question by:cashonly
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
1 Comment
 
LVL 35

Accepted Solution

by:
[ fanpages ] earned 500 total points
ID: 39205917
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

Featured Post

Office 365 Training for IT Pros

Learn how to provision tenants, synchronize on-premise Active Directory, implement Single Sign-On, customize Office deployment, and protect your organization with eDiscovery and DLP policies.  Only from Platform Scholar.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
This article helps those who get the 0xc004d307 error when trying to rearm (reset the license) Office 2013 in a Virtual Desktop Infrastructure (VDI) and/or those trying to prep the master image for Microsoft Key Management (KMS) activation. (i.e.- C…
Learn how to make your own table of contents in Microsoft Word using paragraph styles and the automatic table of contents tool. We'll be using the paragraph styles in Word’s Home toolbar to help you create a table of contents. Type out your initial …
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

717 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question