Is There A Faster Way To Do This?

[CaliKidd]

I am using Excel 2007 and the following code to find blank/empty cells in a large column of data. If blanks exist, I use a variable to count the number of them and I highlight the cell using a sub.

The problem is this loop is slow because it requires every cell to be scanned. Is there a faster way, perhaps using a range object, variant array, find, intersect/union, etc? Unfortunately, I am not well-versed in these so I need some assistance.

For reasons I won't elaborate on, I do not want to use a conditional formatting solution.

For Each Scanned_Cell In Range("A5:A1000000")
[INDENT]Scanned_Cell.Select
If Len(Trim(Scanned_Cell)) = 0 Then
[INDENT]Scanned_Cell.Select
Call Highlight_Cell
Blanks = Blanks + 1
[/INDENT]End If
[/INDENT]Next

Also, instead of finding blank cells, what mods would be required if I wanted to:
1. Assuming the data is numeric, find any cell with a specific value (e.g., 0 (zero))?
2. Assuming the data is numeric, find any cell with a value greater than a certain number (e.g., > 1)?
3. Assuming the data is string, find any cell that is not equal to the letter "A", "B" or "C"?

Thanks in advance.

 

[CaliKidd]

Hmmm, the autofilter approach is proving to be more limited than I anticipated...

In Excel 2007, I believe the max limit is 14000. Can anyone else verify this? If there is a limit, what is in in Excel 2010?

 

[CaliKidd]

Sorry, ignore. This posted twice for some unknown reason.

 

[HarryS]

have you tried cc > -0.0001 and cc < 0.0001

 

[Peter_SSs]

Peter, I'm showing my ignorance again. For my general knowledge, what does the "#" and "0" represent? Once I understand this, it might make it clear why using "0" is faster than using "X".

There is nothing special about "#", it was just a text value I chose at random.

The basic approach of the codes I posted was to make a copy of the column A data, but replace the values we wanted to count/highlight with blanks so they could be easily sorted to the bottom and coloured/counted in one lump.

If an existing cell in column A is blank but does not meet the particular criteria we are looking for, I need in my copy column to put something (anything - I chose "#") in place of that blank or it will not be distinguishable from the other cells that are being made blank especially to count/colour.

Using 0 (note not "0") instead of "#" is a bit faster because Excel can, I assume, sort numbers faster than it can sort text.

Also, were you suggesting to make this change for ALL of those conditional scenarios or just one/some?

I was suggesting the change for every section of code where I used "#" for the reason outlined above.

 

[mirabeau]

Hey Calikidd,

Would you like to consider the following code?

It's pretty straightforward, easy to debug (if ever needed), faster than the ones I posted above, and should do most or all of what you requested with very minor modification.

Sub highlights()
Dim t#
t = Timer
Dim lr&, i&, k&, c As String, a
lr = Range("A" & Rows.Count).End(xlUp).Row
a = Range("A1:A" & lr)
For i = 4 To lr     'change 4 to whatever you want for start row
'In the next line, change >3 to whatever you like, such as ="Ckid" or
'whatever string you want, or to =vbnullstring if you want to count/mark blanks
    If a(i, 1) > 3 Then 'vbNullString Then
        k = k + 1
        c = c & ",A" & i
        If Len(c) > 245 Then
            Range(Right(c, Len(c) - 1)).Interior.Color = vbCyan
            c = vbNullString
        End If
    End If
Next i
Range(Right(c, Len(c) - 1)).Interior.Color = vbCyan
MsgBox "Code took " & Format(Timer - t, "0.000 secs") & _
    Chr(10) & "Count = " & k
End Sub

By the way .End(3) is .End(xlUp)

 

[CaliKidd]

have you tried cc > -0.0001 and cc < 0.0001

Thanks, that worked (in finding a zero currency value). Nice workaround.

There is nothing special about "#", it was just a text value I chose at random.<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>

<o></o>
The basic approach of the codes I posted was to make a copy of the column A data, but replace the values we wanted to count/highlight with blanks so they could be easily sorted to the bottom and coloured/counted in one lump. <o></o>
<o></o>
If an existing cell in column A is blank but does not meet the particular criteria we are looking for, I need in my copy column to put something (anything - I chose "#") in place of that blank or it will not be distinguishable from the other cells that are being made blank especially to count/colour.<o></o>
<o></o>
Using 0 (note not "0") instead of "#" is a bit faster because Excel can, I assume, sort numbers faster than it can sort text.<o></o>
<o></o>
<o></o>
I was suggesting the change for every section of code where I used "#" for the reason outlined above.

OK, that’s what I thought, but I wanted to be sure. Thanks for verifying that. What made me ask was the selection case: Case "A", "B", "C" where the code opted to keep/use the existing value rather than replacing it with a "X". I didn't know if that exception was on purpose or an oversight?

Hey Calikidd,

Would you like to consider the following code?

Excellent! Incredibly fast. Effective. Versatile. No limits. Exactly what I need.

By the way .End(3) is .End(xlUp)

Interesting. When I looked up enumeration values in the help file to try to understand your logic, it gave me the following:

<TABLE id=topTable width="100%"><TBODY><TR id=headerTableRow1><TD align=left></TD></TR><TR id=headerTableRow2><TD align=left>XlDirection Enumeration</TD></TR></TBODY></TABLE>
Specifies the direction in which to move.
Version Information
Version Added: Excel 2007
<TABLE><TBODY><TR><TH>Name</TH><TH>Value</TH><TH>Description</TH></TR><TR><TD>xlDown</TD><TD>-4121</TD><TD>Down.</TD></TR><TR><TD>xlToLeft</TD><TD>-4159</TD><TD>To left.</TD></TR><TR><TD>xlToRight</TD><TD>-4161</TD><TD>To right.</TD></TR><TR><TD>xlUp</TD><TD>-4162</TD><TD>Up.</TD></TR></TBODY></TABLE>
That's why I was confused.

 

[Peter_SSs]

What made me ask was the selection case: Case "A", "B", "C" where the code opted to keep/use the existing value rather than replacing it with a "X". I didn't know if that exception was on purpose or an oversight

Sounds like you have much better code now, which is great, but to complete the explanation on this ..

I didn't replace A, B, C with anything because that task was to highlight/count cells with a value not equal to the letter "A", "B" or "C" so I was just ensuring everything else was blank since it is the blanks that detrmine the count and the colouring.

I guess that I could have replaced A, B, C with 1 as well but without having any idea what proportion of the million rows are A, B, C is is hard to decide if it is best to ..
- save time by not replacing A, B, C at all and putting up with a slightly slower sort, or
- take a little more time to replace A, B, C with 1 and have a slightly quicker sort.

In post #5 Rick tried to get a feel for what the data might be like and your response was fairly noncommittal. Mirabeau also aksed a few times for sample indications. The likely proprtion and grouping of the 'target values' can have an impact on what approach would be the quickest. It was interesting to note that in post #34 you indicated that you had tested with only one blank cell in over a million rows. This seemed very much at one end of the range when in post #6 your response indicated that there could be anything from no blank cells to the whole range being blank.

 

[CaliKidd]

Peter,

I understand your point. It's hard to help develop an ideal solution for someone when they can't provide specifics. Very valid point.

The problem is that I don't have control over the data. I am an analyst that is trying to develop a spreadsheet that will take data provided by a third party (special needs school) and process it in a meaningful way. In order to do that, however, I have to "scrub" the data; otherwise, it's a "garbage in, garbage out" situation. The whole process hasn't even commenced yet, so I have no idea what shape the data will come to me. As a result, I am simply planning for the worst and hoping for the best.

Prior to starting this thread, I developed code on my own, but found it to be slow and inefficient. The speed factor becomes very apparent as the data set increases in size. This is a reflection of my current VBA skill level.

So, what I have done is to seek help from experts like yourself, to come up with code that works smarter, not harder. And to evaluate these various solutions, I have created some "fake" data that pretty well extends to Excel's row limit. My assumption is that if I can get the code to work quickly on a LOT of data, then it should be smoking fast on small data sets.

I hope this extra info makes sense of the situation. It's hard to be more committal when I don't yet know what to expect myself.

I do want to say, Peter, that I appreciate your help and expertise as well as that of everyone else on this forum. All of the solutions offered up were better than my own and I learned a lot of neat techniques along the way, as well as the limitations of some things (e.g., specialcells, autofilter). I like your clever use of sorts to isolate the various conditions I was seeking to evaluate. I am very grateful.

 

[mirabeau]

Calikidd,

Good that you're happy with the outcome of this thread.

And yes, I've found it useful too.

Before leaving, I'll acknowledge Greg's code which looks interesting and instructive.

Regarding your own speed test, with one blank cell near the end of a list of 1million+, there's some codes posted here http://www.mrexcel.com/forum/showthread.php?t=577869 Post#2 that look at this type of question.

A SpecialCells approach is included in these, but a couple of the others can do the job faster. In general, SpecialCells is by no means always the fastest, although often convenient in not-so-large datasets.