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>

 

[Rick Rothstein]

At the end of the day I think you should use my script. With some help from Mark.

Sub Auto_Filter_New()
'Modified 9-3-17 5:00 PM EDT
Application.ScreenUpdating = False
[B][COLOR="#FF0000"]If AutoFilter = True Then AutoFilter = False[/COLOR][/B]
    With Range("A1:F" & Cells(Rows.Count, "A").End(xlUp).Row)
        .Cells.Interior.ColorIndex = xlNone
        .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"
        .Offset(1).Resize(.Rows.Count - 1).SpecialCells(xlCellTypeVisible).Interior.ColorIndex = 3
       .AutoFilter
    End With
Application.ScreenUpdating = True
End Sub

The highlighted line of code does not do what you think it does. Turn on Option Explicit and you will see why. Although I don't think any line of code is needed there, did you perhaps mean this instead...

If ActiveSheet.AutoFilterMode = True Then ActiveSheet.AutoFilterMode = False

 

[JackDanIce]

Is there any difference between:

Cells(Rows.Count, "A").End(xlUp).Row)

vs

Cells(Rows.Count, 1).End(xlUp).Row)

?
Usually I see

Range("A" & Rows.Count)

where a string is used to build the address, but Cells is normally used with two integers arguments instead of an integer and string, (like Resize and Offset) as index numbers faster to evaluate than "A"?
How I remember syntax for

.Offset(1, 0).Resize(LR - 1)

when applying an action to filtered data as in each argument it's (row number, column number) if it helps.

 

[My Aswer Is This]

Try this:
How about this Rick will this work or are there more things here I should change.

Sub Auto_Filter_New()
'Modified 9-3-17 5:43 PM EDT
Application.ScreenUpdating = False
If ActiveSheet.AutoFilterMode = True Then ActiveSheet.AutoFilterMode = False
    With Range("A1:F" & Cells(Rows.Count, "A").End(xlUp).Row)
        .Cells.Interior.ColorIndex = xlNone
        .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"
        .Offset(1).Resize(.Rows.Count - 1).SpecialCells(xlCellTypeVisible).Interior.ColorIndex = 3
      AutoFilter
    End With
Application.ScreenUpdating = True
End Sub

 

[My Aswer Is This]

I guess you could ask numerous question like:
Like what is faster:

Sub My_Data()
Range(Cells(1, 1), Cells(20, 1)).Value = "Bill"
Range("B1:B20").Value = "John"
Range("C1").Resize(20).Value = "Mike"
Range("D1").Value = "Carol": Range("D1:D20").FillDown
[E1:E20].Value = "Sandy"
End Sub

Is there any difference between:

Cells(Rows.Count, "A").End(xlUp).Row)

vs

Cells(Rows.Count, 1).End(xlUp).Row)

?
Usually I see

Range("A" & Rows.Count)

where a string is used to build the address, but Cells is normally used with two integers arguments instead of an integer and string, (like Resize and Offset) as index numbers faster to evaluate than "A"?
How I remember syntax for

.Offset(1, 0).Resize(LR - 1)

when applying an action to filtered data as in each argument it's (row number, column number) if it helps.

 

[Peter_SSs]

How about this Rick will this work or are there more things here I should change.

Yes, there is, (and I don't think you should be so aggressively defensive about people making suggestions/comments).

The red line below needs to be prefixed by a dot.

Sub Auto_Filter_New()
'Modified 9-3-17 5:43 PM EDT
Application.ScreenUpdating = False
If ActiveSheet.AutoFilterMode = True Then ActiveSheet.AutoFilterMode = False
    With Range("A1:F" & Cells(Rows.Count, "A").End(xlUp).Row)
        .Cells.Interior.ColorIndex = xlNone
        .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"
        .Offset(1).Resize(.Rows.Count - 1).SpecialCells(xlCellTypeVisible).Interior.ColorIndex = 3
      [COLOR="#FF0000"][B]AutoFilter[/B][/COLOR]
    End With
Application.ScreenUpdating = True
End Sub

My comments on speed and method:
For me, with 300,000 rows of randomly generated Y/N values, MAIT's code took only just over 1 second to run. I could not come up with any array looping method that was any faster (though I could roughly equal it) and given the concept simplicity of the AutoFilter method, I would stick to MAIT's code, with the slight adjustment mentioned above. FYI, Jack's code from post 37 for me took almost 7 seconds.

 

[My Aswer Is This]

Try this:

Sub Auto_Filter_New()
'Modified 9-3-17 8:56 PM EDT
Application.ScreenUpdating = False
    With Range("A1:F" & Cells(Rows.Count, "A").End(xlUp).Row)
        .Cells.Interior.ColorIndex = xlNone
        .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"
        .Offset(1).Resize(.Rows.Count - 1).SpecialCells(xlCellTypeVisible).Interior.ColorIndex = 3
      .AutoFilter
    End With
Application.ScreenUpdating = True
End Sub

 

[My Aswer Is This]

I'm going to move on and if someone want's to make a new script it's fine with me. We are now up to post #47 . I'm just trying to help people here I'm not able to measure every line of code for speed. I had the dot in previous post but I guess some how I removed it. I'm really glad we have some here who can write code a lot better and faster then mine.

Yes, there is, (and I don't think you should be so aggressively defensive about people making suggestions/comments).

The red line below needs to be prefixed by a dot.



My comments on speed and method:
For me, with 300,000 rows of randomly generated Y/N values, MAIT's code took only just over 1 second to run. I could not come up with any array looping method that was any faster (though I could roughly equal it) and given the concept simplicity of the AutoFilter method, I would stick to MAIT's code, with the slight adjustment mentioned above. FYI, Jack's code from post 37 for me took almost 7 seconds.

 

[Peter_SSs]

I'm going to move on .. I'm really glad we have some here who can write code a lot better and faster then mine.

Did you read all of my post?

MAIT's code took only just over 1 second to run. I could not come up with any array looping method that was any faster ... I would stick to MAIT's code

 

[JackDanIce]

I guess you could ask numerous question like:
Like what is faster:

Because I want to learn what is fastest approach and based on what I've learnt in the past, if I need to change when I'm presented with new information. I.e. I didn't know that using Application.Index in an array with more than 65,536 rows does not work, so now I'm not going to take that approach and this thread has taught me that filtering data for such cases is the fastest approach.

In the past, I didn't understand Resize but constant reading of thread replies then made it click. It's about developing yourself and not being wedded to an idea and refusing to acknowledge alternatives or critiquing if someone else posts a reply with suggested code after code has already been suggested. Surely that's part of the appeal of VBA it's flexible to allow different solutions? For me, I just prefer to know what the fastest approaches are.

For that specifically, Range("A" & Rows.Count).End(xlUp).Row makes more sense than Cells(Rows.Count, "A").End(xlUp).Row, as Cells uses positive integer values as argument parameters. I understand that with Cells(Rows.Count, "A") there is additional processing to convert "A" to 1, so seems redundant to use "A" within Cells, but more appropriate with Range as Range takes a string value as argument parameter. Rather than comment What is Faster, maybe there's a reason why you use Cells(Rows.Count, "A") and can enlighten why it's preferred over, Cells(Rows.Count, 1). Then anyone else reading can say "So that's the reason why, I think I will use it to, or not"

If an MVP or any other person posts code, I'd rather understand what it's doing and why it's been written in such a way. And so many times people post different code solutions to a given problem, it's a way of communal learning. Perhaps I should change my signature to include

Always trying to learn more

?

I agree with this entirely as I see it occurring often on numerous threads

Yes, there is, (and I don't think you should be so aggressively defensive about people making suggestions/comments).

where a reply or throwaway comment comes across as aggressive and not wanted and more importantly adds nothing to the thread or solution offered.

 

[Rick Rothstein]

For that specifically, Range("A" & Rows.Count).End(xlUp).Row makes more sense than Cells(Rows.Count, "A").End(xlUp).Row, as Cells uses positive integer values as argument parameters. I understand that with Cells(Rows.Count, "A") there is additional processing to convert "A" to 1, so seems redundant to use "A" within Cells, but more appropriate with Range as Range takes a string value as argument parameter. Rather than comment What is Faster, maybe there's a reason why you use Cells(Rows.Count, "A") and can enlighten why it's preferred over, Cells(Rows.Count, 1). Then anyone else reading can say "So that's the reason why, I think I will use it to, or not"

First, let me say that I do not actually know the answer... what I am going to give you is my opinion, so accept it or ignore as you please.

Personally, for single cell references, I think Cells is faster than Range, but I'll bet not by enough for you to care. The reason I think Range is slower is because VB has to create the text string in memory (concatenation is not a particularly fast operation as I understand it) and then it has to split the address into a row number and a column number (while the address, say, F9 makes sense to you and seems direct, but to Excel, a spreadsheet is a grid whose rows must be counted down and whose columns must be counted across to get to any single cell in it... a letter does not aid it in that goal). Of course, Cells only can address a single cell while a Range can create multicell contiguous and non-contiguous groups of cell references, so Range definitely has its place. The main benefit of Cells is in loops since you can independently iterate the row and column argument. I also like that the column argument to Cells can be a number (useful, as I said, in a loop) or a letter (easy for a human to read).