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.

 

[Rick Rothstein]

Thanks, all, for your help!

Rick, FYI - I noticed something peculiar about your code. It seems that your approach is to break the data set into large chunks and analyze these chunks sequentially. There is a problem, though. If there are any blank cells in any of the "remaining" cells (i.e., after the last whole chunk), those don't seem to be trapped. Let me explain.

Assume there are 1000000 rows of data and you're using a chunk size of 16000. That creates 62 chunks with 8000 rows remaining (I don't believe these remaining cells are treated as a chunk). So, if there are any blank cells in those remaining rows, they go undetected. I am not certain, but I believe this to be true because I put a blank near the end of the data set and then played around with the chunk size (made them smaller and smaller). Eventually, the blank was detected when it did not fall into a remainder cell, but I lost a lot of speed in going with small chunks.

Interestingly, I decided to go the other way and modify things so that the code makes 1 single, massive chunk of the data set (hence, never any remainder) and it worked fine (and quickly). So, ultimately, I am happy with the solution.

Thanks, much, for your expertise and time.

You are right... I did screw up the chunk method (in my defense, my posting is time-coded at 4:00am and I went to bed for the night not all that long afterwards)... I needed to run the loop in reverse, from bottom to top, not top down. As for the single chunk method... whether that would work or not was the point of two of my questions, but you were not able to give me an answer that would guarantee it will always work. If you ever have enough data and if there are more than 8192 non-contiguous areas of blank ranges, then the single chunk method will fail (8192 is a hard and fast limit to then number on non-contiguous ranges that SpecialCells can handle in Xl2007 and earlier). I will look at my chunk-method code later tonight and patch it so it will work correctly just so you have a working method that is guaranteed to always work.

 

[CaliKidd]

Thanks for the additional info, Rick. I had no idea there was this limit of 8192, so that is helpful to know. It makes sense now why you chose a chunk size of 16000. I thought it was arbitrary at first, but it now appears you were preparing for the worst-case scenario where every other cell was blank. Am I correct in assuming that?

And, just out of curiosity, is there a similar type limit in XL2010? If so, what is it? Just wanted to know for when the day comes I am able to upgrade.

 

[Peter_SSs]

And, just out of curiosity, is there a similar type limit in XL2010? If so, what is it? Just wanted to know for when the day comes I am able to upgrade.

Yes there is, but it is much less likely to be a problem! 2,147,483,648
Here's a reference: http://www.rondebruin.nl/specialcells.htm

 

[CaliKidd]

Thanks, Peter, that's a big limit increase!

Pending Rick's final/revised code solution for finding blanks, I believe that particular problem is pretty much solved.

I wanted to revisit the other issues, however, to see if anyone has any suggested solutions. Once again, it would need to be a fast solution that does not use conditional formatting.

Here they are again:
Instead of finding blank cells, what mods would be required if I wanted to:
1. Find cells with a specific value (e.g., 0 (zero))?
2. Find cells with a value greater than a certain number (e.g., > 1)?
3. Find cells with a value not equal to the letter "A", "B" or "C"?

And one more:
4. Find numeric cells? Non-numeric?

In these cases, a logical expression would need to be evaluated in order to find them. As before, I would want to count and highlight the cells found. I also do not want to sort or extract the data, but rather find them in their original cell positions.

 

[CaliKidd]

Any ideas from the experts?

 

[RoryA]

Use an auto filter then loop through the visible cells, in chunks if necessary. For 4, specialcells also allows choosing text or numbers.

 

[mirabeau]

I wanted to revisit the other issues, however, to see if anyone has any suggested solutions. Once again, it would need to be a fast solution that does not use conditional formatting.

Here they are again:
Instead of finding blank cells, what mods would be required if I wanted to:
1. Find cells with a specific value (e.g., 0 (zero))?
2. Find cells with a value greater than a certain number (e.g., > 1)?
3. Find cells with a value not equal to the letter "A", "B" or "C"?

And one more:
4. Find numeric cells? Non-numeric?

In these cases, a logical expression would need to be evaluated in order to find them. As before, I would want to count and highlight the cells found. I also do not want to sort or extract the data, but rather find them in their original cell positions.

CaliKidd,

Try this one and see if it works OK on the blanks. Number of contiguous cells matters not at all.

If it works OK for you with the blanks it can be simply modified to suit your problems 1, 2 and 3, i.e. these seem essentially the same type of problem. Also probably 4 but I'd like to see a sample of your data before committing on that one.

Sub codamannus()
Dim lng1 As Long, lng2 As Long
Dim ash As Worksheet
Set ash = ActiveSheet
lng1 = ash.Range("A" & Rows.Count).End(xlUp).Row - 4
Application.ScreenUpdating = False
With Sheets.Add
    ash.Range("A5").Resize(lng1).Copy .Cells(1)
    .Cells(2) = 1
    .Cells(2).Resize(lng1).DataSeries
    .Cells(1).Resize(lng1, 2).Sort .Cells(1), 1, Header:=xlNo
    lng2 = .Cells(1).End(4).Row
    .Cells(lng2 + 1, 1).Resize(lng1 - lng2).Interior.Color = vbRed
    .Cells(1).Resize(lng1, 2).Sort .Cells(2), 1, Header:=xlNo
    .Cells(1).Resize(lng1).Copy ash.Range("A5")
Application.DisplayAlerts = False
    .Delete
Application.DisplayAlerts = True
End With
Application.ScreenUpdating = True
MsgBox lng1 - lng2 & " blank cells in ColA"
End Sub

 

[Peter_SSs]

Using many of the concepts of mirabeau's code, but with some slight variations, on my sample data (about 1/3 of the cells are blank) this takes only about half as long.

It also includes any blank after the last non-blank data down to row 1,000,000 as I thought that is what you were asking. Could be wrong in interpreting what you wanted there though.

The code assumes original data in column A only. Is that the case?

<font face=Courier New><br><SPAN style="color:#00007F">Sub</SPAN> ColourAndCountBlanks()<br>    <SPAN style="color:#00007F">Dim</SPAN> rws <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, c <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, z <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br>    <br>    <SPAN style="color:#00007F">Const</SPAN> fr <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN> = 5<br>    <SPAN style="color:#00007F">Const</SPAN> lr <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN> = 1000000<br>    <br>    rws = lr - fr + 1<br>    Application.ScreenUpdating = <SPAN style="color:#00007F">False</SPAN><br>    Columns("A").Insert<br>    <SPAN style="color:#00007F">With</SPAN> Cells(fr, "A").Resize(rws)<br>        .Cells(1, 1).Value = 1<br>        .DataSeries<br>        .Resize(, 2).Sort Key1:=.Cells(1, 2), _<br>            Order1:=xlAscending, Header:=xlNo<br>        z = .Cells(1).Offset(, 1).End(xlDown).Row<br>        c = lr - z<br>        <SPAN style="color:#00007F">If</SPAN> c > 0 <SPAN style="color:#00007F">Then</SPAN><br>            Cells(z + 1, 2).Resize(c).Interior.ColorIndex = 3<br>        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>        .Resize(, 2).Sort Key1:=.Cells(1, 1), _<br>            Order1:=xlAscending, Header:=xlNo<br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>    Columns("A").Delete<br>    Application.ScreenUpdating = <SPAN style="color:#00007F">True</SPAN><br>    MsgBox "Count = " & c<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>

 

[CaliKidd]

@Mirabeau and Peter,

Thanks for your help. Your use of sorts to accomplish the objective is very clever and fast. I am wondering, using Peter's code, what lines would need to change to accomplish 1, 2 and 3? Blanks seem more straightforward since they sort to the very bottom, making them easy to isolate, but finding certain values based on expressions (=, >, <>) would seem to indicate that those values would be somewhere in the midst of the dataset, not necessarily at the top or bottom. How exactly would you isolate those?

P.S. Peter, yes, the data is only in Col. A.

@Rorya,

Alternatively, I would also like to explore your idea on using autofilters to accomplish 1, 2 and 3, so I can compare the speed of the this method to the others. I understand the concept, but I am not sure how to code it. I am unclear how to loop through the visible cells set by the autofilter. Can you or someone offer their expertise in developing this sub?

 

[CaliKidd]

For 4, specialcells also allows choosing text or numbers.

Thanks, I researched the specialcells options and was able to resolve #4.

Issues 1, 2 and 3 still open. Any help would be appreciated.