Selecting all cells with specific text

screech

Active Member
Joined
May 27, 2004
Messages
296
I would like help on some VBA code -- maybe it's too early in the morning but I can't figure it out.

I would like to select every cell on a worksheet that has a particular string of text in the cell. The text I'm looking for is "Rec". It will not be the only text in the cell. I keep trying to use the Find function in my code but I don't think this is the best way to do this. Please help.
 

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.

Von Pookie

MrExcel MVP
Joined
Feb 17, 2002
Messages
13,686
Do you have to use a macro? You can do this easily with the normal Find function in Excel (at least I can do it with 2002).

- Press Ctrl+F to bring up the Find dialog, and enter your search term.
- Click the Options button to display the rest of the dialog box
- Apply the following settings:
- Match Case and Match Entire Cell Contents are not checked
- Lookin: Values[/list]
- Click Find All and a list of all of the cells where the search term is found should be displayed underneath the Find dialog.
- By default, the first item in the list will be selected, and this will also select that cell on the worksheet. Just press Ctrl+A to select all of the items in the list, and this will also select all of those cells on the worksheet.
- Click Close to close the Find dialog box and retain the selected cells.
 

screech

Active Member
Joined
May 27, 2004
Messages
296
Do you have to use a macro? You can do this easily with the normal Find function in Excel (at least I can do it with 2002).

I am using Excel 2000. I do not think I have that option in my Find command. And yes, I would like to have a macro do it automatically as there are several conditions like this that I would like it to check for every time.
 

Von Pookie

MrExcel MVP
Joined
Feb 17, 2002
Messages
13,686
Excel 2000 should have a "Find All" button on the Find dialog, I'm pretty sure.

Anyway--a macro. This seems to work for me:

Code:
Sub test()
Dim c As Range, FoundCells As Range
Dim firstaddress As String

Application.ScreenUpdating = False
With Sheets("Sheet1")
    'find first cell that contains "rec"
    Set c = .Cells.Find(What:="rec", After:=.Cells(Rows.Count, 1), LookIn:=xlValues, LookAt:= _
    xlPart, MatchCase:=False)
    
    'if the search returns a cell
    If Not c Is Nothing Then
        'note the address of first cell found
        firstaddress = c.Address
        Do
            'FoundCells is the variable that will refer to all of the
            'cells that are returned in the search
            If FoundCells Is Nothing Then
                Set FoundCells = c
            Else
                Set FoundCells = Union(c, FoundCells)
            End If
            'find the next instance of "rec"
            Set c = .Cells.FindNext(c)
        Loop While Not c Is Nothing And firstaddress <> c.Address
                
        'after entire sheet searched, select all found cells
        FoundCells.Select
    Else
        'if no cells were found in search, display msg
        MsgBox "No cells found."
    End If
End With
Application.ScreenUpdating = True

End Sub
 

screech

Active Member
Joined
May 27, 2004
Messages
296

ADVERTISEMENT

Excel 2000 should have a "Find All" button on the Find dialog, I'm pretty sure.

I don't see a Find All option on the Find Dialog... probably why I couldn't figure this out by using the Find command. But regardless, I did try your code and it works perfectly! Thank you very much for all of your help! I sincerely appreciate it.
 

screech

Active Member
Joined
May 27, 2004
Messages
296
Hello again,

The code did work perfectly, but I am trying to make a small change to it. The original code searched the entire sheet for the text and added it to the range. I would like to not apply the search to Columns A to H. To make this change, I am trying to confine the search to Columns I through IV. The code that I am using is below but it still adds cells that are in columns A through H. How do I modify this code to tell it where I would like it to search?

Code:
Dim d As Range, dFoundCells As Range
Dim dfirstaddress As String
Columns("I:IV").Select
Set d = Selection.Find(what:="rec", After:=Selection(Rows.Count, 1), LookIn:=xlValues, LookAt:= xlPart, MatchCase:=False)

Thanks!
 

screech

Active Member
Joined
May 27, 2004
Messages
296
Is there anyone that can help me restrict the range of cells that this acts on? I'll keep experimenting in the meanwhile...
 

Fergus

Well-known Member
Joined
Mar 10, 2004
Messages
1,174
In fifth line of Kristy's code try replacing
Code:
With Sheets("Sheet1")
with
Code:
With Sheets("Sheet1").Range("I:IV")

Edit : corrected Range from I:VI to I:IV :oops:
 

Forum statistics

Threads
1,144,568
Messages
5,725,046
Members
422,590
Latest member
Mikeyyy

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top