Selecting a range based on values of two other columns-CodePudding

I have two columns with the range O6:P that evaluate the row of data in J and display Pass or Fail. The results in O and in P are not always the same. One can display Passing and the other will have failed. The code I have here works for the most part. Except that it is selecting the range in column J where either O or P are equal to PASS. I need it to only select the range where both columns are Passing. is there a way to split this up so that it will only select the range in J where both values in the row for O and P are passing?

Dim lastrow As Long
  Dim xRg As Range, yRg As Range, nRg As Range, mRg As Range

    'Selecting range = to PASS
    With ShNC1
        lastrow = .Cells(.Rows.Count, "J").End(xlUp).Row
        Application.ScreenUpdating = False
        For Each xRg In .Range("O6:P" & lastrow)
            If UCase(xRg.Text) = "PASS" Then
                If yRg Is Nothing Then
                    Set yRg = .Range("J" & xRg.Row)
                Else
                    Set yRg = Union(yRg, .Range("J" & xRg.Row))
                End If
            End If
        Next xRg

    End With

    If Not yRg Is Nothing Then yRg.Select

CodePudding user response：

You could loop through just O and use Offset to check P:

Dim lastrow As Long
  Dim xRg As Range, yRg As Range, nRg As Range, mRg As Range

    'Selecting range = to PASS
    With ShNC1
        lastrow = .Cells(.Rows.Count, "J").End(xlUp).Row
        Application.ScreenUpdating = False
        For Each xRg In .Range("O6:O" & lastrow)
            If UCase(xRg.Text) = "PASS" And UCase(xRg.Offset(, 1).Text) = "PASS" Then
                If yRg Is Nothing Then
                    Set yRg = .Range("J" & xRg.Row)
                Else
                    Set yRg = Union(yRg, .Range("J" & xRg.Row))
                End If
            End If
        Next xRg

    End With

    If Not yRg Is Nothing Then yRg.Select

CodePudding user response：

#Ms-Excel#

Enter or copy this formula into a blank cell where you want to output the result:

=INDEX(A2:A15,MODE(MATCH(A2:A15,A2:A15,0)))

Tips: In this formula: A2:A15: is the data list that you want to find the greatest number of times a text occurs.