Hide rows not containing specific text

[netbeui]

Hello, new to this forum!

I have searched a lot for answers to this prior to posting the question; please forgive me if I have missed it somewhere.


The question:

1)I want to hide rows in a spreadsheet if a cell in that row does not contain specific text. (There may be "other" text in the cell with the specific text; that's OK)

2)2 InputBoxes (1) for range and (1) for test value would be nice.


The code I have tried so far does not "sense" a cell if there is other than the specific text in with it (in same cell).


Thank you in advance,

netbeui

 

[Luke M]

Hi netbeui!

Thanks for doing a search first. =)

This was a bit of a twist on the normal request to hide based on criteria, I liked the challenge. Here's what I came up with. Note that it is not case-sensitive (hope that's okay).

[pre]

Sub HideCriteria()
Dim SearchRange As Range
Dim SearchValue As String
On Error Resume Next
Set SearchRange = Application.InputBox("Select a range of cells", Type:=8)
On Error GoTo 0
SearchValue = InputBox("What are you looking for?")
Application.ScreenUpdating = False
If Not SearchRange Is Nothing And SearchValue <> "" Then
For Each c In SearchRange
If InStr(1, c.Value, SearchValue, vbTextCompare) > 0 Then
c.EntireRow.Hidden = False
Else
c.EntireRow.Hidden = True
End If
Next c
End If

Application.ScreenUpdating = True
End Sub

[/pre]

 

[netbeui]

Luke M:

You are truly an Excel Ninja, thank you so much, it works beautifully! I have not seen anything like it anywhere. And you figured it out so quickly.


I have spent a lot of time on this, and was currently studying VB code books, and will be sure to analyze this an learn from you.


Thank you again A++

-netbeui