selective filtering so that select rows only if cell A or cell B contains a value

[psychopedia]

Hi:
I have a dataset that contains baseball record, I want to pick only one team to look at, so is there anyway I can apply filter on HomeTeam and VisitTeam so that if either HomeTeam or VisitTeam contains the team name, say PHI, the who row stays?

E.g: I have the data
HomeTeam VisitTeam
PHI CLE
ATL NYC
DET PHI

I want the first row and third row.

Thank you!

 

[alansidman]

try this:

Option Explicit


Sub foo()
    Dim lr As Long
    lr = Range("A" & Rows.Count).End(xlUp).Row
    Dim i As Long


    For i = 2 To lr
        If Range("A" & i) = "PHI" Or Range("B" & i) = "PHI" Then
            Range("A" & i).EntireRow.Hidden = False
        Else: Range("A" & i).EntireRow.Hidden = True
        End If
    Next i
End Sub

 

[BlueHornet]

Alternatively, if you prefer a formula-only method, you can set up a Helper Column, say D:D, and a single cell that you'll use to specify the team of interest. Let's say that cell is C1, with the entry "PHI". Then the formula in D:D for all of the rows of interest could be something like

=IF( OR( A2 = $C$1, B2 = $C$1), 1, 0)

to list a column of 1's on rows that contain the team you want to examine. Then filter the sheet on that column, looking at all "1" values.

 

[psychopedia]

try this:

Option Explicit


Sub foo()
    Dim lr As Long
    lr = Range("A" & Rows.Count).End(xlUp).Row
    Dim i As Long


    For i = 2 To lr
        If Range("A" & i) = "PHI" Or Range("B" & i) = "PHI" Then
            Range("A" & i).EntireRow.Hidden = False
        Else: Range("A" & i).EntireRow.Hidden = True
        End If
    Next i
End Sub

Thank you! That works for me. Really appreciate it.