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]

What exactly you are trying to do is not clear. You say you want to count the number of blanks, but do you really want to count them all the way down to Row 1000000 even if the blanks you are counting are way below the last piece of data? Also, after getting a count of the blanks, you appear to only want to select one blank cell, no matter how many there are... is that correct? If so, which blank... the first or last (your code looks like it will select the last one in the column)? I think a little more description of what you want is needed.

 

[CaliKidd]

Sorry if I was not clear. Let me try again. I am trying to find the number of empty/blank cells in a large column of data (range runs from cell A5 down to A1000000). As the loop executes, if an empty cell is found, then it is highlighted (by a sub I call) and I increment a counter variable called Blanks by 1 (this allows me to get a total when the loop finishes).

Functionally, this code gets the job done, but it is slow since the data set is so large.

I am A) trying to find a faster way, and B) wanting to know how to get the other enumerated variations done in a fast way also.

I hope this makes more sense.

 

[CaliKidd]

Just to be clear, the data runs all the way down to row 1000000 and every blank cell would be counted and highlighted.

 

[Rick Rothstein]

Because you are using XL2007 (and not XL2010), there are some limitation for the method I am thinking of that could be reached given you have 1000000 rows of data. Please answer all of the following questions as best you can.

1) Can you give me an idea of the maximum amount of blanks cells you might expect to have in your data?

2) Do your blank cells tend to be "clumped together" or would you expect lots of unconnected ranges of blanks? Approximately how many individual groups might you expect as a maximum?

3) What column are we talking about?

4) What kinds of data is in this column... constants or formulas (or maybe a mixture of constants and formulas)?

 

[CaliKidd]

I am working on a spreadsheet that only analyzes data provided by a user.
I don't own the data. I simply import the data provided by the user into my spreadsheet.

1. Cannot say. In a perfect world, there would be no empty cells. But the reality is there could be "holes" in the data or the entire range could be blank if the user forgets to include it in his data. So, if any exist, I am trying to find them, highlight them, and provide a total so I can get the owner of the data to find & fix them. Think data quality control.

2. Again, cannot say. I simply need to be able to find them if they exist.

3. Column A. Range starts on row 5. The last row could vary, but for the purpose of this exercise, assume the data ends on row 1000000.

4. No formulas. Just values.

Sorry, I do not have XL2010 and will not likely get it anytime soon.

 

[Zaf]

Have a look at this. May be what you are after...

Public Sub TEST()
Application.ScreenUpdating = False
Range("A5:A1000000").Select
Dim r As Long, c As Long
r = Selection.Rows.Count
c = Selection.Columns.Count
ActiveCell.Resize(1, 1).Select
Dim Count As Long
Count = 0
Dim AllData() As Variant
ReDim AllData(r, c)
For i = 1 To r
For j = 1 To c
AllData(i, j) = ActiveCell.Offset(i - 1, j - 1).Value
     'Doesn't have to be If Trim(AllData(i, j)) = "" Then to count blanks
     'Could be If AllData(i,j)=0 then for counting cells that contain zero
     'Could be If AllData(i,j)>1 then for counting cells greater than one
     'Could be If AllData(i,j)="A" or AllData(i,j)="B" or AllData(i,j)="C" then for particular strings...
     
     If Trim(AllData(i, j)) = "" Then
     Count = Count + 1
     'And highlight the cell
          With ActiveCell.Offset(i - 1, j - 1).Interior
          .Pattern = xlSolid
          .PatternColorIndex = xlAutomatic
          .ThemeColor = xlThemeColorAccent2
          .TintAndShade = 0.599993896298105
          .PatternTintAndShade = 0
          End With
     End If
     
Next j
Next i
Application.ScreenUpdating = True
End Sub

 

[Rick Rothstein]

Give this a try and see if it does what you want...

Sub CountBlanksAndHighlightThem()
  Dim X As Long, LastDataRow As Long, TotalBlanks As Long, Blanks As Range
  Const StartRow As Long = 5
  Const ChunkSize As Long = 16000
  On Error Resume Next
  LastDataRow = Cells.Find(What:="*", SearchOrder:=xlRows, SearchDirection:=xlPrevious, LookIn:=xlFormulas).Row
  Application.ScreenUpdating = False
  For X = StartRow To LastDataRow Step ChunkSize
    Set Blanks = Cells(X, "A").Resize(ChunkSize).SpecialCells(xlCellTypeBlanks)
    If Not Blanks Is Nothing Then
      TotalBlanks = TotalBlanks + Blanks.Count
      Blanks.Interior.ColorIndex = 3
      Set Blanks = Nothing
    End If
  Next
  Application.ScreenUpdating = True
  MsgBox "There are a total of " & TotalBlanks & " blank cells in Column A."
End Sub

Note: The code only counts down to the last row with any data on it (no matter what column that piece of data is in), not to Row 1000000.

 

[RoryA]

If you can sort the data first on the column of interest, you can use an autofilter and specialcells(xlcelltypevisible) to identify any set of cell types you like.

 

[CaliKidd]

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.