To display number of blank cells from a range by using filters

Question

I used conditional formatting to display blank cells and created macros by filtering by name of every individual who has blanks on the worksheet. Now I need to display the number of blank cells for each individual. How do I go about it .

Sub Displayblanks_John_Doe()
 '
' Displayblanks_John_Doe Macro
'
' Keyboard Shortcut: Ctrl+Shift+J
'
Range("A3:L26").Select
Range("A3:L26,N3:N26").Select
Range("N3").Activate
Range("A3:L26,N3:N26,R3:R26").Select
Range("R3").Activate
Range("A3:L26,N3:N26,R3:R26,W3:W26").Select
Range("W3").Activate
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
    "=LEN(TRIM(A3))=0"
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Interior
    .Pattern = xlGray8
    .PatternColorIndex = xlAutomatic
    .ColorIndex = xlAutomatic
End With
Selection.FormatConditions(1).StopIfTrue = False
ActiveSheet.Range("$A$3:$W$26").AutoFilter Field:=7, Criteria1:= _
    "John Doe"
    Dim mycount As Long
    mycount = Application.WorksheetFunction.CountBlank(ActiveSheet.Range("$A$3:$W$26"))
    MsgBox mycount
End Sub

Answer 1

Use the Range.SpecialCells method with the xlCellTypeVisible property to isolate the cells left visible by the AutoFilter method.

The With ... End With statement statement can help you reference specific areas within your workbook without resorting to .Select¹ and .Activate¹.

Sub Displayblanks_John_Doe()
    ' Displayblanks_John_Doe Macro
    ' Keyboard Shortcut: Ctrl+Shift+J
    Dim mycount As Long

    With Worksheets("Sheet1")   'you should know what worksheet you are on
        If .AutoFilterMode Then .AutoFilterMode = False
        With .Range("A3:L26,N3:N26,R3:R26,W3:W26")
            .FormatConditions.Add Type:=xlExpression, Formula1:="=LEN(TRIM(A3))=0"
            With .FormatConditions(.FormatConditions.Count)
                .Interior.Pattern = xlGray8
                .StopIfTrue = False
                .SetFirstPriority
            End With
        End With
        With .Range("A3:W26")
            .AutoFilter Field:=7, Criteria1:="John Doe"
            'there has to be visible cells because we haven't excluded the header row yet
            With Intersect(.SpecialCells(xlCellTypeVisible), _
                           .SpecialCells(xlCellTypeBlanks))
                mycount = .Cells.Count
                MsgBox mycount
            End With
        End With
        If .AutoFilterMode Then .AutoFilterMode = False
    End With

End Sub

The above code counts visible cells that are truly blank; not cells that could be zero-length strings returned by formulas (e.g. "" ).

I realize that your code is not finished yet; simply because it doesn't actually do anything but report teh number of blank visible cells. One of the first steps would be to find the last populated cell in column G and use that as the extent of the filter range. A quick method of accomplishing this is to reference the Range.CurrentRegion property is a With ... End With statement as soon as you've referenced the worksheet; e.g.

With Worksheets("Sheet1")   'you should know what worksheet you are on
    With .Cells(1, 1).CurrentRegion
        'do all of your work here
    End With
End With

This may not be appropriate for your particular situation as you wish to start at row 3 but the Range.Resize property and Range.Offset property can take care of this.

With Worksheets("Sheet1")   'you should know what worksheet you are on
    With .Cells(1, 1).CurrentRegion
        With .Resize(.Rows.Count - 2, .Columns.Count).Offset(2, 0)
            'do all of your work here
        End With
    End With
End With

Recorded macro code is a good place to start but you will quickly find that it is very verbose and often records actions that are not desired. Get used to cleaning your code up after a macro recording session and you will soon find that you are writing the code from scratch.

---

¹ _{See How to avoid using Select in Excel VBA macros for more methods on getting away from relying on select and activate to accomplish your goals.}