Excel Search / Find Function Hide All That Do Not Match 1

[PugnaxX]

Hi All,

Background:
I have a database (glorified list) in Excel that contains 8 Fields. The first field of each row contains part numbers separated by a comma and a space. Other characteristics specific to the part number or part numbers such as tool manufacturer and tool type appear in the other fields for each row. This worksheet is linked to a master workbook (different file), so that it updates automatically. This worksheet will available to manufacturing and will be read-only. The links for this worksheet were created by selecting each source column in the master, copying the entire column and then paste special: Link (this causes all cells with no value in the master to have a value of 0). This was done so that whenever a new row in the master is created, the links in this dependent workbook would not need to be updated. (If there is a better way to achieve this let me know)

Goal: I am trying to make this dependent workbook really easy to use for the folks in manufacturing that will need to search it, so I am trying to designate one cell or text box at the top as a search field. I will then have a button that runs the custom search macro to find all the matches to the one "search field" cell. I understand that the general response to this will be "Why not use Cntrl F (Excel's built-in functionality) or add a Find button to call Excel's built-in search dialog)", however this is not an option for the powers that be. I have read thread707-1318170.

Desired Behavior: When a search is performed I would like all rows that DO NOT MATCH the search criteria to be hidden with the exception of rows 1-3 which house the title, column headings and search cell, so that the only data rows that are visible are the ones that contain a match. (Alternatively, if all data rows were initially hidden, the search would make visible only the rows that contain a match)

What I've Tried: I've tried recording Find, using the Find and Find Next VBA scripts and I have tried using some code from thread707-1318170. I have also tried initially hiding all rows (4-655360) and then performing the search to unhide the matches (the problem with this is that Excel will not search hidden cell Values). Because this is a linked file and I have multiple part numbers in cells I realize I need to use the Find Option: Look in: Values.

Here's some of the code that I have tried, just to try and get closer to a solution, In this code I actually Hide the Results that Match, but this takes forever:

Sub SearchTest()


Dim X As Range
Set X = Cells.Find(What:=Range("B2"), LookIn:=xlValues, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
, SearchFormat:=False)

If Range("B2").Value = 0 Then
MsgBox ("Must Enter A Contact Number, Tool Number, Tool Type, etc... 0 is not specific enough")
Else
While Not X Is Nothing

X.EntireRow.Hidden = True
Set X = Cells.Find(What:=Range("B2"), LookIn:=xlValues, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
, SearchFormat:=False)

Wend

Range("A1").EntireRow.Hidden = False
Range("A2").EntireRow.Hidden = False
Range("A3").EntireRow.Hidden = False
MsgBox "No More Found"
End If
End Sub

Thanks for any advice you may have!!!! I seem to have searched as much as I can and fiddled around and still can't seem to get it right so I figured it was time to talk you, the great experts of VBA! Thanks again!

 

[SkipVought]

Hi,

"...first field of each row contains part numbers separated by a comma and a space. "

Please post a few examples of this.

"...If there is a better way to achieve this let me know..."

faq68-5829. I would not recommend using links.

"...all rows that DO NOT MATCH the search criteria to be hidden ..."

Data > AutoFilter. If you need more than 2 search criteria, then hide all rows with data, and do sucessive FINDS, This works on HIDDEN rows.

    Dim r As Range, lPrevRow As Long
    Set r = Cells.Find(sFindValue)
    lPrevRow = 0
    Do
        If Not r Is Nothing Then
            r.EntireRow.Hidden = False
            lPrevRow = r.Row
            Set r = Cells.FindNext(r)
        End If
    Loop (While lPrevRow <= r.Row)

Skip,
[sub]
To be safe on the [red]FOURTH[/red],
Don't take a [red]FIFTH[/red] on the [red]THIRD[/red]
Or you might not come [red]FORTH[/red] on the [red]FIFTH[/red]
[red][highlight blue]FORTH[/highlight][/red][white][highlight red]WITH[/highlight][/white] [/sub]

 

[PugnaxX]

Hey Skip!

Thanks for your help so far! The reason I haven't gone with MS Query is there are comments that have pictures for fill, so that when you mouse over the tool number a picture of the tool appears and I'm not sure that I could get Query to show the pictures with mouseover (not too familiar with MSQuery).

I wish I could use AutoFilter, I've used it extensively in the past, but because there could be a single part number that appears more than one is several different "groups" of contact part numbers, the list would be unmanageable. It is also desired that employees could simply search any text from one box instead of clicking through the drop downs to filter. This way they go to one place, type in any characters they wish to find and hit one button. They would also not have to worry about which AutoFilters are on (dark blue dropdown arrows which are hard to see) and setting them all back to Show ALL.

Here's an example of the data (the contact p/n's are wrapped in one cell and the group of part numbers from that cell share the characteristics tool type, tool number, manufacturer & manuf. p/n for that specific row)(Note: These are not all the fields for each row, but should give you an idea of what I'm working with):

CONTACT P/N TOOL TYPE TOOL NUMBER MANUF. MANUF.P/N

234322, 275843, CRIMP T-CRP-11-01 DMC HX4-180
298323, 227732


273783, 643564, EXTRACT T-EXT-11-01 DMC 3323123-02
234322, 435412


649832, 232219, FRAME T-FRM-11-04 AMP 91029389
198273

I've tried the code above that you posted, and it seems it goes through all hidden rows and unhides the ones that have a value. How can I modify this to unhide the rows that contain a certain value? I tried replacing "sFindValue" with

"What:=Range("B2"), LookIn:=xlValues, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
, SearchFormat:=False"

so that I would be able to search for the value that is in Range B2 (my designated "search box"), but this was unsuccessful.

I also tried:

Set sFindValue = Cell.Value= Range("B2") to no avail.

Thanks again for any expertise you can spare!!!!

-PugnaxX

 

[SkipVought]

"... single part number that appears more than one is several different "groups" of contact part numbers..."

AutoFilter Criteris Contains

"...search any text from one box ..."

List all parts, individually, pick from a user defined control, apply the choice to AutoFilter with the desired criteria.

"They would also not have to worry about which AutoFilters are on (dark blue dropdown arrows which are hard to see) and setting them all back to Show ALL"

YOU control the AutoFilter, not them.

"Set sFindValue = Cell.Value= Range("B2") to no avail."

    Dim r As Range, lPrevRow As Long
    Set r = Cells.Find(range("B2"))
    lPrevRow = 0
    Do
        If Not r Is Nothing Then
            r.EntireRow.Hidden = False
            lPrevRow = r.Row
            Set r = Cells.FindNext(r)
        End If
    Loop (While lPrevRow <= r.Row)

the initial hiding must be done with Row.Hidden = True and NOT the AutoFilter



Skip,
[sub]
To be safe on the [red]FOURTH[/red],
Don't take a [red]FIFTH[/red] on the [red]THIRD[/red]
Or you might not come [red]FORTH[/red] on the [red]FIFTH[/red]
[red][highlight blue]FORTH[/highlight][/red][white][highlight red]WITH[/highlight][/white] [/sub]

 

[PugnaxX]

Hey Skip,

Thanks so much for your help!
I ended up changing the way I approached this project and set up a dependent file that actually opens the source file, queries the file looking for how many rows contain data and then copies this only the data within the range and pastes the values in pre-formatted cells in a dependent file. It also copies over the comments that contain the pop up pictures and I plan to get this update functionality to occur upon open event.

I had a little trouble with the code above that you had posted, because I believe that it may not have been looking at values, but formulas which didn t work in the linked file. It is working beautifully for the new method of dependent file updating, but I have one question.

When an item is not found, the search will hang and it seems like it is going through the thousands of non-hidden rows that have no values. I would like to contrain it to search only the range that contains values and then if not found then spit out a message box saying "Search Criteria Not Found" I think I m on the right path, but I can't seem to get it to work. Could you point me in the right direction?

'First find the last row in the data (thanks to some more 'code from another post of Skip's):

Windows("TOOLS_DATABASE_EngineeringTest.xls").Activate
Dim lRow As Long, lRowFirst As Long, lRowLast As Long
With Worksheets("TOOLS_MAIN").[A1].CurrentRegion
lRowFirst = .Row
lRowLast = lRowFirst + .Rows.Count - 1
For lRow = lRowFirst To lRowLast
Next
End With

'Then Set Search Range:

Range(Cells(4, 1), Cells(lRowLast, 9)).Select ???

Dim r As Range, lPrevRow As Long
Set r = Cells.Find(Range("A2"))
lPrevRow = 0
Do
If Not r Is Nothing Then
r.EntireRow.Hidden = False
lPrevRow = r.Row
Set r = Cells.FindNext(r)
End If
Loop While lPrevRow <= r.Row

End If

Where would I put a MsgBox "Search Criteria Not Found" ?

Thanks again for all of you help!

 

[PugnaxX]

Hi All,

I've still been fiddling with this and still can't seem to figure out how to set a search range and display a message if string not found. Can anyone point me in the right direction?

I at least figured out how to give a message for an entered 0 or no entry. Here's the code again:
===========================================================
Sub SearchDataBase()

'The following figures out how many rows contain data
Dim lRow As Long, lRowFirst As Long, lRowLast As Long

With Worksheets("Sheet1").[A4].CurrentRegion
lRowFirst = .Row
lRowLast = lRowFirst + .Rows.Count - 1
For lRow = lRowFirst To lRowLast
Next

'The following line if uncommented will show you the value of lRowLast
' MsgBox lRowLast
End With

Range(Cells(4, 1), Cells(lRowLast, 1)).EntireRow.Hidden = True
Range("1:3").EntireRow.Hidden = False

'Checks A2, if zero value then gives error message otherwise will perform search

If Range("A2").Value = 0 Then
MsgBox ("INVALID ENTRY! You Must Enter a PART NUMBER OR a String of TEXT that you wish to search for. Please Try Again.")
Range("A2").Select

Else

'The following searches all hidden cells for a match to the value in cell A2

Dim r As Range, lPrevRow As Long
Set r = Cells.Find(Range("A2"))
lPrevRow = 0
Do
If Not r Is Nothing Then

r.EntireRow.Hidden = False
lPrevRow = r.Row

Set r = Cells.FindNext(r)

Else

MsgBox "Entry Not Found"
End If
Loop While lPrevRow <= r.Row

End If

Range("1:3").EntireRow.Hidden = False

End Sub
===========================================================


Thanks for any advice you can offer!

-PugnaxX

 

[PugnaxX]

I linked a textbox (controls toolbar) and placed it on the worksheet and linked it to the Range("A2") so it feels more like a real search box. Still having trouble with the Message to display if nothing is found (stop search if nothing found also).

Anybody?

 

[SkipVought]

"Still having trouble with the Message to display ..."

What kind of trouble?

Please be specific.

Skip,
[sub]
To be safe on the [red]FOURTH[/red],
Don't take a [red]FIFTH[/red] on the [red]THIRD[/red]
Or you might not come [red]FORTH[/red] on the [red]FIFTH[/red]
[red][highlight blue]FORTH[/highlight][/red][white][highlight red]WITH[/highlight][/white] [/sub]

 

[PugnaxX]

I am having trouble with the following section of code (full code available above):
============================================================
'The following searches all hidden cells for a match to the value in cell A2

Dim r As Range, lPrevRow As Long
Set r = Cells.Find(Range("A2"))
lPrevRow = 0
Do
If Not r Is Nothing Then

r.EntireRow.Hidden = False
lPrevRow = r.Row

Set r = Cells.FindNext(r)

Else

MsgBox "Entry Not Found"
End If
Loop While lPrevRow <= r.Row
============================================================
I can't figure out how to first set the range that the search is performed on (only those rows that actually contain data) and how to exit loop if no match is found. If nothing is found, I need to also display a message box:

MsgBox "No Matches Found"

The first thing I need to do is to check to see what the last row is that contains data and then set the range, I think I can figure out where the last data row is with this:

============================================================
'The following figures out how many rows contain data
Dim lRow As Long, lRowFirst As Long, lRowLast As Long

With Worksheets("Sheet1").[A4].CurrentRegion
lRowFirst = .Row
lRowLast = lRowFirst + .Rows.Count - 1
For lRow = lRowFirst To lRowLast
Next
End With
============================================================

So to review this is what I'm trying to do:

>Set Search Range to rows that have data
>If no match is found stop search and display message box


Thanks for any help you can offer!

PugnaxX

 

[SkipVought]

Dim r As Range, lPrevRow As Long
    Set r = Cells.Find(Range("A2"))
    lPrevRow = 0
    Do
        If Not r Is Nothing Then
        
            r.EntireRow.Hidden = False
            lPrevRow = r.Row
            
            Set r = Cells.FindNext(r)
        
        Else
       
            MsgBox "Entry Not Found"
            exit do
        End If
    Loop While lPrevRow <= r.Row

"Set Search Range to rows that have data"

WHY? If there's no data, there's nothing to inhibit what you want to happen.



Skip,
[sub]
To be safe on the [red]FOURTH[/red],
Don't take a [red]FIFTH[/red] on the [red]THIRD[/red]
Or you might not come [red]FORTH[/red] on the [red]FIFTH[/red]
[red][highlight blue]FORTH[/highlight][/red][white][highlight red]WITH[/highlight][/white] [/sub]

 

[PugnaxX]

What I've found is, if no match is found, the code runs on forever. I was thinking if I could limit the range that it searches to only the range of rows that contain data, then it would result in 1) Quicker Searches & 2) Elimination of the Hanging problem.

If there is another solution, I am definitely open to it, but I still need to have a message pop up if nothing is found.


Thanks Again!

PugnaxX

 

[SkipVought]

    Do
        If Not r Is Nothing Then
        
            r.EntireRow.Hidden = False
            lPrevRow = r.Row
            
            Set r = Cells.FindNext(r)
        
        Else
       
            MsgBox "Entry Not Found"
            exit do
        End If[b]
    Loop While lPrevRow < r.Row[/b]

Skip,
[sub]
To be safe on the [red]FOURTH[/red],
Don't take a [red]FIFTH[/red] on the [red]THIRD[/red]
Or you might not come [red]FORTH[/red] on the [red]FIFTH[/red]
[red][highlight blue]FORTH[/highlight][/red][white][highlight red]WITH[/highlight][/white] [/sub]

 

[PugnaxX]

There are a couple issues with the new code.

These are the results that you get using the following code variations:

Loop While lPrevRow < r.Row

>NO Hanging when criteria NOT FOUND (this is great!)
>DOES NOT FIND ALL instances of partial search string (ex: "2" instead of full part number "259269") (it needs to be able to find partial search strings)
>If not found does NOT show message box (needs to show this on not found)

&

Loop While lPrevRow <= r.Row

>Hangs (code runs forever) when criteria NOT FOUND
>DOES FIND ALL instances of partial search string (ex: "2" instead of full part number "259269")
>If not found does NOT show message box


Please help! Any advice would be most appreciated!

Thanks again,

PugnaxX

 

[SkipVought]

If you're looking in more than one COLUMN, then also test the column...

Loop While (lPrevRow < r.Row AND iPrevCol < r.Column)

Skip,
[sub]
To be safe on the [red]FOURTH[/red],
Don't take a [red]FIFTH[/red] on the [red]THIRD[/red]
Or you might not come [red]FORTH[/red] on the [red]FIFTH[/red]
[red][highlight blue]FORTH[/highlight][/red][white][highlight red]WITH[/highlight][/white] [/sub]

 

[PugnaxX]

The search part now works GREAT! However, the only thing wrong with it is when something is not found it does NOT display the MsgBox "Entry Not Found".

If something is not found the macro just ends. Any thoughts? SOOO close to having this DONE!

Thanks again for all of your help!

PugnaxX

 

[PugnaxX]

Anybody have any ideas? I'm in kind of a bunch, tomorrow is my last day here at work for a while and I need to have this working for release? I still can't seem to get the message box to pop up, it's as if it skips the Else code that contains the Entry Not Found Message Box altogether.

Any advice you could offer would be great! This is what the code looks like now:

===========================================================
'Checks A2, if zero value then gives error message otherwise will perform search

If Range("A2").Value = 0 Or Range("A2") = "" Then
MsgBox "INVALID ENTRY! You Must Enter a PART NUMBER OR a String of TEXT that you wish to search for. Please Try Again.", vbCritical, "INVALID DATA ENTERED"
Range("A2").Select

Else

'The searchbox on the worksheet is linked to cell A2, when a value is entered into the searchbox it appears in A2
'This was done because it was easier to have Excel check the value of A2 than the searchbox itself
'The following searches all hidden cells for a match to the value in cell A2

Dim r As Range, lPrevRow As Integer, lPrevCol As Integer


Set r = Cells.Find(Range("A2"))
lPrevRow = 0

Do
If Not r Is Nothing Then

r.EntireRow.Hidden = False
lPrevRow = r.Row

Set r = Cells.FindNext(r)

Else

MsgBox "Entry Not Found"
Exit Do
End If

Loop While lPrevRow < r.Row And lPrevCol < r.Column


End If

============================================================


Thanks again for all of your help in the past, you've been great!


Sincerely,

PugnaxX

 

[PHV]

What about this ?
Set r = Cells.Find(Range("A2"))
lPrevRow = 0
Do While lPrevRow < r.Row And lPrevCol < r.Column
If Not r Is Nothing Then
r.EntireRow.Hidden = False
lPrevRow = r.Row
Set r = Cells.FindNext(r)
Else
MsgBox "Entry Not Found"
Exit Do
End If
Loop

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[PugnaxX]

Hey PHV,

I just tried it, it looks like the search works fine still with that reordering, but still no message when not found.

I stepped through the code for the search and when "Not r Is Nothing" is not true (string not found), it skips over the else statement that contains the message box altogether.

?

Thanks again,

PugnaxX

 

[PugnaxX]

Anybody have any more advice? I'm on my last day!



Regardless of any more advice you may be able to spare, I do want to thank you, Skip and PHV for your help thus far though. I really appreciate the time you've taken to try to help me!



Sincerely,

PugnaxX