Jason posted a question on Sep 2, 2019 - and I will show you bellow what was the question and the answer which helped me too. Thank you
But i want to go further - then "search box hide row that don't contain" and also to clear/don't show cells that are different in the same row than search box. So, i would like to have on the row only what search box contain.
From Jason Bing:
I have a sheet with a list of customer orders called (FTBJOB2) the data is contained in cells A to AP of the table starting from row 13
I Know how to make a combo box that places whatever is typed, in a specific cell. In this case it will put the result in cell ("AQ12")
I would love a code that would dynamically hide all rows from Row 13 down to 997 that don't contain the text in cell ("AQ12") in any of the cells of that row
This is to create a google style search box using anything to do with the customer orders
I already have buttons to hide rows based on the value in column D (this contains the job status) so I will need to unhide these first in the code so they see result for each job.
This will let the guys see orders that only pertain to whatever they are searching for.
is this a code that will live in the worksheet or in a separate macro?
Any help would be greatly appreciated
Thanks wonderful people
Regards
Jason Bing
-----------------------/
Forum answer (Sep 02, 2019) :
This works for me
- rows 13:997 are hidden
- rows looped and searched to find required text and first found cell in each row is added to range Vizible
- rows in range Vizible unhidden
Place code in the SHEET module (avoids need to qualify range references with sheet)
- call it after value in AQ12 has been updated: (see code bellow)
Sub HideRows()
Dim r As Long, What As String, Where As Range, Found As Range, Vizible As Range
Rows("13:997").EntireRow.Hidden = True
What = "*" & Range("AQ12") & "*"
Set Where = Range("A1:AP1")
For r = 12 To 996
On Error Resume Next
Set Found = Nothing
Set Found = Where.Offset(r).Find(What)
On Error GoTo 0
If Not Found Is Nothing Then
If Vizible Is Nothing Then Set Vizible = Found Else Set Vizible = Union(Vizible, Found)
End If
Next r
If Not Vizible Is Nothing Then Vizible.EntireRow.Hidden = False
End Sub
----------------------------------/
I solved the problem that Jason had, but I would like on the row to show only what is in the search box and all other cells (that contain different info that search box) to be blank.
Thank you in advance
Magda
But i want to go further - then "search box hide row that don't contain" and also to clear/don't show cells that are different in the same row than search box. So, i would like to have on the row only what search box contain.
From Jason Bing:
I have a sheet with a list of customer orders called (FTBJOB2) the data is contained in cells A to AP of the table starting from row 13
I Know how to make a combo box that places whatever is typed, in a specific cell. In this case it will put the result in cell ("AQ12")
I would love a code that would dynamically hide all rows from Row 13 down to 997 that don't contain the text in cell ("AQ12") in any of the cells of that row
This is to create a google style search box using anything to do with the customer orders
I already have buttons to hide rows based on the value in column D (this contains the job status) so I will need to unhide these first in the code so they see result for each job.
This will let the guys see orders that only pertain to whatever they are searching for.
is this a code that will live in the worksheet or in a separate macro?
Any help would be greatly appreciated
Thanks wonderful people
Regards
Jason Bing
-----------------------/
Forum answer (Sep 02, 2019) :
This works for me
- rows 13:997 are hidden
- rows looped and searched to find required text and first found cell in each row is added to range Vizible
- rows in range Vizible unhidden
Place code in the SHEET module (avoids need to qualify range references with sheet)
- call it after value in AQ12 has been updated: (see code bellow)
Sub HideRows()
Dim r As Long, What As String, Where As Range, Found As Range, Vizible As Range
Rows("13:997").EntireRow.Hidden = True
What = "*" & Range("AQ12") & "*"
Set Where = Range("A1:AP1")
For r = 12 To 996
On Error Resume Next
Set Found = Nothing
Set Found = Where.Offset(r).Find(What)
On Error GoTo 0
If Not Found Is Nothing Then
If Vizible Is Nothing Then Set Vizible = Found Else Set Vizible = Union(Vizible, Found)
End If
Next r
If Not Vizible Is Nothing Then Vizible.EntireRow.Hidden = False
End Sub
----------------------------------/
I solved the problem that Jason had, but I would like on the row to show only what is in the search box and all other cells (that contain different info that search box) to be blank.
Thank you in advance
Magda
