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>

 

[Peter_SSs]

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.

Yes, its why I used

.. slight readjustment of this 2 to a 1 (as it was in the original code) would ..

 

[My Aswer Is This]

Lets try this again:

Try this:

Sub Auto_Filter()
'Modified 9-3-17 6:55 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

 

[MARK858]

Hi M.A.I.T, I think What Peter and Rick are questioning is the use of A2, as autofilter requires a header row. As you have it row 2 wouldn't be included in the formatting.

The normal way around it is to offset and resize the range i.e.

Sub Auto_Filter()
'Modified 9-3-17 6:55 AM EDT
Application.ScreenUpdating = False

    With Range("[COLOR="#FF0000"]A1[/COLOR]: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"
        [COLOR="#FF0000"].Offset(1).Resize(.Rows.Count - 1).[/COLOR]SpecialCells(xlCellTypeVisible).Interior.ColorIndex = 3
        .AutoFilter
    End With
    
Application.ScreenUpdating = True
End Sub

or if there is never any data below the filter range then just Offset(1) would suffice.

 

[JackDanIce]

As suggested in thread #13 ?

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

 

[My Aswer Is This]

Yes I always have trouble with Filters and keep forgetting about this offset thing. Loops are easier for me but then people complain saying loops are too slow. Thanks for reminding me once again about offset. I think you mentioned this to me before and I forgot. Thanks for your help.

Hi M.A.I.T, I think What Peter and Rick are questioning is the use of A2, as autofilter requires a header row. As you have it row 2 wouldn't be included in the formatting.

The normal way around it is to offset and resize the range i.e.

Sub Auto_Filter()
'Modified 9-3-17 6:55 AM EDT
Application.ScreenUpdating = False

    With Range("[COLOR=#ff0000]A1[/COLOR]: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"
        [COLOR=#ff0000].Offset(1).Resize(.Rows.Count - 1).[/COLOR]SpecialCells(xlCellTypeVisible).Interior.ColorIndex = 3
        .AutoFilter
    End With
    
Application.ScreenUpdating = True
End Sub

or if there is never any data below the filter range then just Offset(1) would suffice.

 

[MARK858]

Yes I always have trouble with Filters... I think you mentioned this to me before and I forgot. Thanks for your help.

No problem, trust me you will get used to using filters

 

[Rick Rothstein]

Forgive me I have one extra line of uneeded code.

For those who might find this interesting, that one line of unneeded code cost one-half second extra in the execution speed for the 300,000 record set the OP said he had... on my computer, 7.96 seconds with it as opposed to 7.45 seconds without it.

 

[JackDanIce]

@Rick Rothstein would you mind putting run time for Code in #13 ? I thought for 300k rows, it would be faster to loop over an array; which is what M.A.I.T commented they usually code with but some find loops slower than filters?

 

[MARK858]

which is what M.A.I.T commented they usually code with but some find loops slower than filters?

I think you will find M.A.I.T. is referring to looping over a range not an array.

 

[Rick Rothstein]

@Rick Rothstein would you mind putting run time for Code in #13 ?
?

I get a "Type Mismatch" error on this line of code (when x = 1, so the loop never runs)...

[table="width: 500"]
[tr]
	[td]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[/td]
[/tr]
[/table]