Hide rows in Excel automatically

Hi, I figured out how to do this years ago, but I just can't remember now when I need it again. I have a bunch of data in cell range: A7:0444.

I have a search box where the user can put in any value they want in B2.

All I want is when a user searches a term in B2, all those rows that don't contain that search value get hidden. When the search box is empty all rows are visible.

Simple?

hi,
Perhaps You should assign that code to event:

Sub hide_rows()

Application.Calculation = xlCalculationManual

for i=444 to 7 step -1
if workshetfunction.countif(range("a"& i &":o"& i),Range("B2").value)=0 then
range("a"& i &":o"& i).EntireRow.hidden=true
end if
next i

Application.Calculation =xlCalculationAutomatic
End Sub

I got it to work for hiding rows, thank you. I would like when the search box B2 is empty, that when the user hits search on an empty cell, all the rows become visible again. Right now they don't, the user has to go in a un-hide them.

You are right! I forgot about it

Sub hide_rows()

Application.Calculation = xlCalculationManual

if range("b2").value="" then
range("A7:0444").EntireRow.hidden=FALSE
exit sub
end if

for i=444 to 7 step -1
if workshetfunction.countif(range("a"& i &":o"& i),Range("B2").value)=0 then
range("a"& i &":o"& i).EntireRow.hidden=true
end if
next i

Application.Calculation =xlCalculationAutomatic
End Sub

When I cut and paste the code in I get an error message:

Method Range of Object _Global failed

The code is then directing me here:

Sub hide_rows()

Application.Calculation = xlCalculationManual

If Range("b2").Value = "" Then
Range("A7:0444").EntireRow.Hidden = False (THIS LINE IS YELLOW)
Exit Sub
End If

For I = 444 To 7 Step -1
If workshetfunction.CountIf(Range("a" & I & ":o" & I), Range("B2").Value) = 0 Then
Range("a" & I & ":o" & I).EntireRow.Hidden = True
End If
Next I

Application.Calculation = xlCalculationAutomatic
End Sub

Any Ideas?

Sub hide_rows()

Application.Calculation = xlCalculationManual

If Range("b2").Value = "" Then
Range("A7:o444").EntireRow.Hidden = False (THIS LINE IS YELLOW) -> It should be 'o' instad of '0' and now it works properly!
Exit Sub
End If

For I = 444 To 7 Step -1
If workshetfunction.CountIf(Range("a" & I & ":o" & I), Range("B2").Value) = 0 Then
Range("a" & I & ":o" & I).EntireRow.Hidden = True
End If
Next I

Application.Calculation = xlCalculationAutomatic
End Sub