AutoFilter to find blank cells

Question

I am trying to apply an autofilter in VBA for three different criterias in the same field. Once I have applied the filter I would like to find all those cells that are blank, can anyone advise?

    Sub ApplyAutoFiler()
    Dim ws As Worksheet
    Dim I, j, NumberOfErrors As Long
    IsErrors = False

    Set ws = Sheets("Assessments")

    NumberOfErrors = 0
    Dim Z As Range
    Set Z = Cells(4, 3).EntireColumn.Find("*", SearchDirection:=xlPrevious)
    If Not Z Is Nothing Then
        NumberOfRows = Z.Row
    End If

    For I = 4 To NumberOfRows

   With ws
   .AutoFilterMode = False
  .Range("W4:AA4").AutoFilter Field:=1, Criteria1:=Array("A", "B", "C"), Operator:=xlFilterValues
  .Cells.SpecialCells(xlCellTypeBlanks).Interior.Color = 65535
  .AutoFilterMode = False

   End With

   Next I


   End Sub

enter image description here

I ended up doing this as a nested if statement

If Range("W" & i).Value = "A" Or Range("W" & i).Value = "B" Or Range("W" & i).Value = "C" Then
     If Range("AD" & i).Value = "" Then
        Range("AD" & CStr(i)).Interior.ColorIndex = 3
        NumberOfErrors = NumberOfErrors + 1
     End If
  End If

What is `I` in your `Cells(I,J)`... at the moment it equals to Zero which causes an error... — Kazimierz Jawor, Apr 15 '13 at 12:34
I, Is the row number J, Is the column They are both set as Long — user1643333, Apr 15 '13 at 12:39
About how many rows does NumberOfRows evaluate to? There is a limitation of around 8k non-contiguous cells when using SpecialCells. If it's more than that, you may want to use a loop. Also, do you just want to check column AA for blank cells or any cells in columns W through AA? — sous2817, Apr 15 '13 at 14:38
At the minute it's only checking 580 but this can change to at least a 1000+. I am filtering on column W and I only want to check those in AA for blanks. — user1643333, Apr 15 '13 at 14:44
Let me come back to you on this. Thanks for the screenshot. Makes it much clearer. — Siddharth Rout, Apr 15 '13 at 15:44
Just saw your comments in the below post. Please confirm this : You want to check for blank cells in Col AA for every a,b,c in Col W — Siddharth Rout, Apr 15 '13 at 15:53
I want to check all blank cells in column AA when column W is filter on a, b and c. — user1643333, Apr 15 '13 at 15:59

score 1 · Answer 1 · answered Apr 15 '13 at 12:38

1

This seemed to get me close (it also assumes you have a worksheet called "Assessments"):

   Sub ApplyAutoFiler()
   Dim ws As Worksheet

   Set ws = Sheets("Assessments")

 With ws
      .AutoFilterMode = False
      .Range("A:AZ").AutoFilter Field:=23, Criteria1:=Array("a", "b", "c"), Operator:=xlFilterValues
      .Cells.SpecialCells(xlCellTypeBlanks).Interior.Color = 65535
      .AutoFilterMode = False
End With
End Sub

answered Apr 15 '13 at 12:38

sous2817

3,915
2
33
34

Thank You, This seems to be doing something but every time I try and run it the spreadsheet does not respond – user1643333 Apr 15 '13 at 13:05
Can you expand on what "does not respond" means? Does it lock up, or nothing happens? If I had to guess, without seeing your sheet, this part should be cleaned up "Range("A:AZ")." to be only the rows you actually want filtered. – sous2817 Apr 15 '13 at 13:11
It locks up and I have to go onto task manager to close it down. I changed the range for only the rows I needed and the filter is working but then locks again – user1643333 Apr 15 '13 at 13:38
It sounds like you have other things going on (like a lot of calculations). Can you provide a sense of what else is going on in the sheet that may be causing the problem? Maybe try my code on a new sheet just to make sure it's not the code that's causing the problem. – sous2817 Apr 15 '13 at 14:01
I did try it on a new sheet, and I could see the filter work but after that it locked. what I also meant to include was that the blanks I am looking for are in a different column, sorry I am new to vba. – user1643333 Apr 15 '13 at 14:12
I'm having trouble figuring out why your sheet is locking up. I'm not having that issue on my end. Can you post a sample on a file sharing site so I can take a look? This line: .Cells.SpecialCells(xlCellTypeBlanks).Interior.Color = 65535 will highlight in yellow all of the cells in your filtered range that are blank. – sous2817 Apr 15 '13 at 14:19
I am getting an error stating SpecialCells out of Range, I'm not sure if it is because there is a lot of empty cells on the sheet, I only wish to see if there are it column AA. I have updated my coding. – user1643333 Apr 15 '13 at 14:33

score 1 · Answer 2 · edited Dec 23 '19 at 19:52

Something I just discovered today about filtering for blanks using VBA code. Be sure to include this in ALL code where you need to have blank cells:

' Get Rows with blanks
WorkRange.AutoFilter Field:=1, Criteria1:="=", Operator:=xlOr, Criteria2:="=" & ""

' Hides Rows with blanks ... same idea with the "<>" for operator
WorkRange.AutoFilter Field:=1, Criteria1:="<>", Operator:=xlOr, Criteria2:="<>" & ""

The first criteria gets true blank cells and those cells with hidden/non-printable characters, the 2nd criteria gets those cells containing an empty string. Excel user-interface handles this nicely, but VBA code requires both criteria.

This undocumented caveat just cost me several hours of debugging, not to mention a few choice words from my manager about "I thought we were removing the blanks from these columns..."

Just thought I would share, in the hopes of saving you all some headaches.

Headache successfully avoided! – Andrew Keeton Dec 23 '19 at 19:49 — Andrew Keeton, Dec 23 '19 at 19:49

score 1 · Answer 3 · answered Jun 05 '17 at 12:43

I know this treads had been quite long. But just want to share. To filter out blank cells, you could use autofilter using the following criteria:

Worksheets("sheet name").Range("A1").autoFilter Field:=18, Criteria1:=(Blanks)

"Field" refers to the column numbers. As for "Criteria1", it can be either

Criteria1:=(Blanks)

or

Criteria1:="="

or

Criteria1:=""

Siddharth Rout · Answer 4 · 2013-04-15T16:09:59.767

0

You don't need VBA for this. You can use Conditional Formatting for this. See this example

In the CF rule, set this formula

=AND($AA5="",OR($W5="a",$W5="b",$W5="c"))

ScreenShot

enter image description here

If you still want VBA then see this

Sub Sample()
    Dim blnkRange As Range, rng As Range, aCell As Range
    Dim ws As Worksheet

    Set ws = ThisWorkbook.Sheets("Assessments")

    With ws
        '~~> Sample range for testing purpose
        Set rng = .Range("W4:AA11")

        .AutoFilterMode = False

        With rng
            '~~> Filter on "a","b","c"
            .AutoFilter Field:=1, Criteria1:=Array("a", "b", "c"), Operator:=xlFilterValues
            '~~> Then filter on blanks on Col AA
            .AutoFilter Field:=5, Criteria1:="="

            '~~> Using offset. Assuming that Row 4 has headers
            Set blnkRange = .Offset(1, 0).SpecialCells(xlCellTypeVisible).Cells
        End With

        .AutoFilterMode = False
    End With

    '~~> This will give you the blank cells in Col AA
    If Not blnkRange Is Nothing Then
        For Each aCell In blnkRange
            '~~> Color the blank cells red in Col AA
            If aCell.Column = 27 Then aCell.Interior.ColorIndex = 3
        Next
    End If
End Sub

edited Apr 15 '13 at 16:09

answered Apr 15 '13 at 15:03

Siddharth Rout

147,039
17
206
250

The blanks I am trying to find are in a different column. column W will never be blank, it's column AA that could be. – user1643333 Apr 15 '13 at 15:25
I will be happy to amend the above code if you show me an example? – Siddharth Rout Apr 15 '13 at 15:26
I have added a screenshot of how the data could be, does this make it clearer? – user1643333 Apr 15 '13 at 15:40
Thanks for helping, unfortunately I am getting run-time error '5' on this line: Set blankRange = .Offset(1, 0).SpecialCells(xlCellTypeBlanks).Cells and I can't seem to figure out the problem – user1643333 Apr 17 '13 at 08:59
What have you set for `Set rng = .Range("W4:AA11")`? – Siddharth Rout Apr 17 '13 at 09:18
It was set as .Range("W4:AA4"), I also tried looping through the rows again and using NumberOfRows. – user1643333 Apr 17 '13 at 09:25
Change AA4 to the AA where is the last row which has data in Col AA – Siddharth Rout Apr 17 '13 at 09:43
The problem with that is the Last Cell could possible change, that's why I tried to loop through each row to find the number of rows. – user1643333 Apr 17 '13 at 10:20
You don't need to loop to find the last row of AA. See this link http://stackoverflow.com/questions/11169445/error-finding-last-used-cell-in-vba – Siddharth Rout Apr 17 '13 at 10:22
Ahh thanks, that has helped. However now I can't get the filter to work. – user1643333 Apr 17 '13 at 11:41

AutoFilter to find blank cells

4 Answers4