Search a column for a value, hide rows that do not contain that value

[Blanchetdb]

Hi

So I am looking to have a code that will search thru column P (range P2:P2000); search for the name of a person ex. Jane Doe (value in cell C6). If found, the corresponding row would be visible but rows that do have than name within that column, the row would be hidden

So I only want to be able to see the rows that contain the name entered in cell C6

any help would be appreciated

thank you
Dan

 

[Anfinsen]

I think this is what you are looking for.

Sub HideRows()

Dim r As Long
Dim LastRow As Long
LastRow = Cells(Rows.Count, "P").End(xlUp).Row
For r = LastRow To 1 Step -1
If Cells(r, "P") = Range("C6")
Rows(r).EntireRow.Hidden = True
End If
Next r

End Sub

 

[Blanchetdb]

I think this is what you are looking for.

Sub HideRows()

Dim r As Long
Dim LastRow As Long
LastRow = Cells(Rows.Count, "P").End(xlUp).Row
For r = LastRow To 1 Step -1
If Cells(r, "P") = Range("C6")
Rows(r).EntireRow.Hidden = True
End If
Next r

End Sub

thank you but the code provided hides all the rows that matches the value in column P and value in cell C6 .... I need all rows that does not match the name in cell 6 and value in column P to be hidden

 

[Anfinsen]

Just changed the = to <>

Sub HideRows()

Dim r As Long
Dim LastRow As Long
LastRow = Cells(Rows.Count, "P").End(xlUp).Row
For r = LastRow To 1 Step -1
If Cells(r, "P") <> Range("C6")
Rows(r).EntireRow.Hidden = True
End If
Next r

End Sub

 

[johnnyL]

How about:

Sub HideRows()
    Range("P7:P2000").AutoFilter Field:=1, Criteria1:=Range("C6").Value
End Sub

 

[Blanchetdb]

Just changed the = to <>

Sub HideRows()

Dim r As Long
Dim LastRow As Long
LastRow = Cells(Rows.Count, "P").End(xlUp).Row
For r = LastRow To 1 Step -1
If Cells(r, "P") <> Range("C6")
Rows(r).EntireRow.Hidden = True
End If
Next r

End Sub

that worked ... thanks

but I forgot to consider that I have info in rows 1 to 7 that MUST remain visible at all time; so only row 8 and onward can be hidden if the criteria is met... I apologize for the omission.

 

[Anfinsen]

Sub HideRows()

Dim r As Long
Dim LastRow As Long
LastRow = Cells(Rows.Count, "P8").End(xlUp).Row
For r = LastRow To 1 Step -1
If Cells(r, "P") <> Range("C6")
Rows(r).EntireRow.Hidden = True
End If
Next r

End Sub

 

[Blanchetdb]

Sub HideRows()

Dim r As Long
Dim LastRow As Long
LastRow = Cells(Rows.Count, "P8").End(xlUp).Row
For r = LastRow To 1 Step -1
If Cells(r, "P") <> Range("C6")
Rows(r).EntireRow.Hidden = True
End If
Next r

End Sub

I added Then at the end of:

If Cells(r, "P") <> Range("C6")

but I still get an error

 

[johnnyL]

@Blanchetdb, is post #5 visible to you?

 

[Blanchetdb]

@Blanchetdb, is post #5 visible to you?

actually just saw it .... I will try it out and let you know