Need VBA to Highlight Rows with all N's

[Stephen_IV]

Good morning,

I have over 300,000 rows of data in Columns A-F. The rows have either N or Y in them. What I need to do is to highlight all rows that have just N's in them. I do not want a formula and Code will help! Thanks in advance.
<style type="text/css">
table.tableizer-table {
font-size: 12px;
border: 1px solid #CCC ;
font-family: Arial, Helvetica, sans-serif;
}
.tableizer-table td {
padding: 4px;
margin: 3px;
border: 1px solid #CCC ;
}
.tableizer-table th {
background-color: #104E8B ;
color: #FFF ;
font-weight: bold;
}
</style>
<table class="tableizer-table">
<thead><tr class="tableizer-firstrow"><th>N</th><th>N</th><th>N</th><th>N</th><th>N</th><th>N</th></tr></thead><tbody>
<tr><td>Y</td><td>N</td><td>N</td><td>N</td><td>N</td><td>Y</td></tr>
<tr><td>N</td><td>N</td><td>N</td><td>Y</td><td>N</td><td>N</td></tr>
<tr><td>N</td><td>N</td><td>N</td><td>Y</td><td>N</td><td>N</td></tr>
<tr><td>Y</td><td>N</td><td>N</td><td>N</td><td>N</td><td>N</td></tr>
<tr><td>Y</td><td>N</td><td>N</td><td>N</td><td>N</td><td>Y</td></tr>
<tr><td>N</td><td>N</td><td>N</td><td>Y</td><td>N</td><td>N</td></tr>
<tr><td>N</td><td>N</td><td>N</td><td>Y</td><td>N</td><td>N</td></tr>
<tr><td>N</td><td>N</td><td>N</td><td>Y</td><td>N</td><td>N</td></tr>
<tr><td>N</td><td>N</td><td>N</td><td>N</td><td>N</td><td>Y</td></tr>
<tr><td>N</td><td>N</td><td>N</td><td>Y</td><td>N</td><td>N</td></tr>
<tr><td>N</td><td>N</td><td>N</td><td>Y</td><td>N</td><td>N</td></tr>
<tr><td>N</td><td>N</td><td>N</td><td>Y</td><td>N</td><td>N</td></tr>
<tr><td>N</td><td>N</td><td>N</td><td>Y</td><td>N</td><td>N</td></tr>
<tr><td>Y</td><td>N</td><td>N</td><td>N</td><td>N</td><td>Y</td></tr>
<tr><td>N</td><td>N</td><td>N</td><td>Y</td><td>N</td><td>N</td></tr>
<tr><td>N</td><td>N</td><td>N</td><td>N</td><td>N</td><td>N</td></tr>
<tr><td>N</td><td>N</td><td>N</td><td>Y</td><td>N</td><td>N</td></tr>
<tr><td>N</td><td>N</td><td>N</td><td>Y</td><td>N</td><td>N</td></tr>
<tr><td>N</td><td>N</td><td>N</td><td>Y</td><td>N</td><td>N</td></tr>
<tr><td>N</td><td>N</td><td>N</td><td>N</td><td>N</td><td>N</td></tr>
</tbody></table>

 

[Stephen_IV]

Thanks JackDanIce and My Aswer Is This! I appreciate your guidance. What I meant and failed to convey myself is if the row had either NNNNNN or YNNNNN. Thanks Again!

 

[My Aswer Is This]

Try this:

Sub Auto_Filter()
'Modified 9-2-17 7:45 PM EDT
Application.ScreenUpdating = False

    With Range("A2:F" & Cells(Rows.Count, "A").End(xlUp).Row)
        .AutoFilter Field:=1, Criteria1:="Y", Operator:=xlOr, Criteria2:="N"
        .AutoFilter Field:=2, Criteria1:="N"
        .AutoFilter Field:=3, Criteria1:="N"
        .AutoFilter Field:=4, Criteria1:="N"
        .AutoFilter Field:=5, Criteria1:="N"
        .AutoFilter Field:=6, Criteria1:="N"
        .SpecialCells(xlCellTypeVisible).Interior.ColorIndex = 3
        .AutoFilter
    End With
    
Application.ScreenUpdating = True
End Sub

 

[JackDanIce]

Try:

Sub Auto_Filter()
    
    Dim LR              As Long
    Dim x               As Long
    Dim i               As Long
    Dim arr()           As Variant
    Dim arrPattern(1 To 2)    As Variant
    Const delim         As String = "|"
    
    arrPattern(1) = "NNNNNN"
    arrPattern(2) = "YNNNNN"
    
    Application.ScreenUpdating = False
        
    With ActiveSheet
        If .AutoFilterMode Then .AutoFilterMode = False
        .Cells.Interior.ColorIndex = xlNone
        LR = .Cells(.Rows.Count, 1).End(xlUp).row
        arr = .Cells(1, 1).Resize(LR, 7).Value
        For x = LBound(arr, 1) To UBound(arr, 1)
            If InStr(arrPattern(1) & delim & arrPattern(2), Join(Application.Index(arr, x, 0), "")) * Len(Join(Application.Index(arr, x, 0), "")) > 0 Then arr(x, UBound(arr, 2)) = True
        Next x
        
        With .Cells(1, 1).Resize(UBound(arr, 1), UBound(arr, 2))
            .Value = arr
            .AutoFilter Field:=UBound(arr, 2), Criteria1:=True
            .Offset(1).Resize(UBound(arr, 1) - 1, UBound(arr, 2) - 1).SpecialCells(xlCellTypeVisible).Interior.ColorIndex = 4
        End With

        .AutoFilterMode = False
        .Cells(1, UBound(arr, 2)).Resize(UBound(arr, 1)).ClearContents
    End With
    
    Application.ScreenUpdating = True
    Erase arr
    Erase arrPattern
       
End Sub

 

[Rick Rothstein]

Try this:

Sub Auto_Filter()
'Modified 9-2-17 7:45 PM EDT
Application.ScreenUpdating = False

    With Range("A2:F" & Cells(Rows.Count, "A").End(xlUp).Row)
        [B][COLOR="#FF0000"].AutoFilter Field:=1, Criteria1:="Y", Operator:=xlOr, Criteria2:="N"[/COLOR][/B]
        .AutoFilter Field:=2, Criteria1:="N"
        .AutoFilter Field:=3, Criteria1:="N"
        .AutoFilter Field:=4, Criteria1:="N"
        .AutoFilter Field:=5, Criteria1:="N"
        .AutoFilter Field:=6, Criteria1:="N"
        .SpecialCells(xlCellTypeVisible).Interior.ColorIndex = 3
        .AutoFilter
    End With
    
Application.ScreenUpdating = True
End Sub

Since the only possible values in the (filled) cells are Y or N, there is no need to set the filter for Field 1 because the OP is looking for either a Y or N there... every filled line automatically matches that criteria... so the red highlighted line can be deleted.

 

[My Aswer Is This]

And how do you come to this conclusion:
"Since the only possible values in the (filled) cells are Y or N,"

I have never seen him state the only values will be Y or N

Since the only possible values in the (filled) cells are Y or N, there is no need to set the filter for Field 1 because the OP is looking for either a Y or N there... every filled line automatically matches that criteria... so the red highlighted line can be deleted.

 

[Rick Rothstein]

And how do you come to this conclusion:
"Since the only possible values in the (filled) cells are Y or N,"

I have never seen him state the only values will be Y or N

From the original message... "The rows have either N or Y in them."

 

[My Aswer Is This]

OK I see that now. Forgive me I have one extra line of uneeded code.

From the original message... "The rows have either N or Y in them."

 

[My Aswer Is This]

OK here is a modified script to correct one unneeded line of my script:
This is assuming there will never be anything in Column "A" but a "N" or a "Y"

Sub Auto_Filter()
'Modified 9-3-17 12:36 AM EDT
Application.ScreenUpdating = False
    With Range("A2:F" & Cells(Rows.Count, "A").End(xlUp).Row)
        .AutoFilter Field:=2, Criteria1:="N"
        .AutoFilter Field:=3, Criteria1:="N"
        .AutoFilter Field:=4, Criteria1:="N"
        .AutoFilter Field:=5, Criteria1:="N"
        .AutoFilter Field:=6, Criteria1:="N"
        .SpecialCells(xlCellTypeVisible).Interior.ColorIndex = 3
        .AutoFilter
    End With
    
Application.ScreenUpdating = True
End Sub

 

[Peter_SSs]

Unless I have missed it, there doesn't seem to be any mention of the existence or not of a header row, but given the OP's acceptance of the earlier code, like other responders I'm assuming there is a header row in row 1.
If that is so, the slight readjustment of this 2 to a 1 (as it was in the original code) would be required, otherwise row 2 will be coloured whether or not it meets the criteria.

    With Range("A[COLOR="#FF0000"][B]2[/B][/COLOR]:F" & Cells(Rows.Count, "A").End(xlUp).Row)

 

[Rick Rothstein]

Unless I have missed it, there doesn't seem to be any mention of the existence or not of a header row, but given the OP's acceptance of the earlier code, like other responders I'm assuming there is a header row in row 1.
If that is so, the slight readjustment of this 2 to a 1 (as it was in the original code) would be required, otherwise row 2 will be coloured whether or not it meets the criteria.

    With Range("A2:F" & Cells(Rows.Count, "A").End(xlUp).Row)

Which, interestingly, is what he had originally in Message #3 before he modified it in Message #12 to account for the OP's Y or N comment about the first field.