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>

 

[MARK858]

Not at home to dig them out but there was a thread on here Nate got involved in and there is one on Stack Overflow that done some timings.
If I remember right fastest to slowest were the syntaxes
Cells(1, 1)
Cell's (1, "A")
Range("A1")

When I am home I will dig them out but you were obviously talking milliseconds.

 

[JackDanIce]

@Rick Rothstein Pretty much same reasons as me (incl my understanding of the technical reasons/opinions given) and is why I prefer Cells (but use numbers exclusively), though agree about using say "A" to make it easier code to read.

But (for me) Range("A1") is easier to understand for the cell reference, than Cells(1, "A") so if it's for ease of code, reading Range("A" & Rows.Count) makes more sense for the concatenation of column Letter to row number, than Cells(Rows.Count, "A") as used in Excel functions (i.e. not SUM(1 & "A":10 & "A") but SUM(A1:A10) )

If looping or iterating, then easier to understand which "part" is being changed, the row number or column number. With respect to RESIZE and OFFSET, being applied afterwards, the same arguments are in the same positions, that is Cells(row_num, col_num), Resize(row_num, col_num) and Offset(row_num, Col_num), which IMO suits Cells(row_num, col_num) better than Cells(row_num, col_str) whereas Range(col_str & row_num) "reads" better if for a reading perspective or when string concatenation is used to create a Range object, especially with non-continuous cells. And yes,

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

Again, these are all personal opinions based on the information we've read or learned and it's then understanding reasons or preferences for other styles and evaluating whether that makes sense for you to use or not. It's not about making unhelpful comments or taking offence if other code is suggested and I apologise if my suggestions and thread replies gave that impression.

@MARK858 I think I read that post ages ago too and yeah it's not huge differences at all though I think [A1] was suggested as slowest? Be good to post it anyway, thanks.

 

[MARK858]

I think that this is the stack overflow link that I was thinking of

https://stackoverflow.com/questions/36073943/range-vs-cells-run-times

and this was the MrExcel link but I don't think reading it again it is as relevant


https://www.mrexcel.com/forum/lounge-v-2-0/258950-cells-1-1-vs-range-a1.html

 

[MARK858]

And just to add Charles Williams take on evaluate...

https://fastexcel.wordpress.com/201...to-make-excels-evaluate-method-twice-as-fast/

 

[Rick Rothstein]

I think that this is the stack overflow link that I was thinking of

https://stackoverflow.com/questions/36073943/range-vs-cells-run-times

Thanks for posting that link. While logic dictated (to me) that Range was slower, it is actually a lot slower than I thought it would be. The link says you might see the difference if used against couple/three hundred thousand rows... I honestly thought the speeds would be close enough that the time difference would not show up no matter how many rows were tested.

 

[MARK858]

All I will say is I have never tested myself to see if I get the same results which is normally my preferred method

 

[My Aswer Is This]

That is some interesting read.
Never knew cells was faster then Range.
Learn something new every day.

Thanks for posting that link. While logic dictated (to me) that Range was slower, it is actually a lot slower than I thought it would be. The link says you might see the difference if used against couple/three hundred thousand rows... I honestly thought the speeds would be close enough that the time difference would not show up no matter how many rows were tested.

 

[shg]

Regarding Charles Williams on the Evaluate method: Evaluate doesn't require the = sign, but you should use it anyway ...

Sub AddThatEqualsSign()
  Dim i             As Long
  Dim d             As Double
  Dim f             As Single
  Const n           As Long = 100000

  f = Timer
  For i = 1 To n
    d = Evaluate("rand()")
  Next i
  Debug.Print Format((Timer - f) / (i - 1), "0.000 000 000")
  Beep
  
  f = Timer
  For i = 1 To n
    d = Evaluate("=rand()")
  Next i
  Debug.Print Format((Timer - f) / (i - 1), "0.000 000 000")
  Beep
End Sub

 

[Rick Rothstein]

Regarding Charles Williams on the Evaluate method: Evaluate doesn't require the = sign, but you should use it anyway ...

Interesting... so I am guessing that in order to pass the text argument over to Excel formula processor, the Evaluate function must be concatenating the equal sign on to the front of the text when it is missing and doing that is what takes the extra time. Very interesting.