Counting Visible Blank cells in a row

2
votes

I have a data set with a large number of blank fields in each column. I would like to count the number of blank cells in each column after I've applied some arbitrary filters to other column(s).

I've gotten this to work in a sub with the following

 Sub whatever()
 Dim myrange As Range
 Set myrange = Worksheets("Sheet1").Range("a1:a100")
 myrange.SpecialCells(xlCellTypeVisible).SpecialCells(xlCellTypeBlanks).Count     
 End Sub

But when I try to put it in a UDF like so

 Function CountBlankVisible(myrange As Range)
 CountBlankVisible = myrange.SpecialCells(xlCellTypeVisible).SpecialCells(xlCellTypeBlanks).Count
 End Function

It seems to be counting every cell in the range regardless of cell type. Any ideas why this would work in a sub but not as a function? is it possible to get this count some other way?

2
excelvba
possible duplicate of VBA UDF gives different answer when called from Excel vs VBA – brettdj
thanks @brettdj. for those who don't want to click the link the SpecialCells method doesn't behave properly in a UDF. – Anthony Bird

2 Answers

1
votes

Excel UDF has some limitations (when called from worksheet). You can read about them here.

Here is working example:

Function CountBlankVisible(myrange As Range)
    Dim c As Range
    For Each c In myrange
        If c.RowHeight > 0 And IsEmpty(c.Value) Then _
            CountBlankVisible = CountBlankVisible + 1
    Next
End Function
1
votes

As an alternative to simoco's code:

Function CountBlankVisible(myrange As Range)
    Dim c As Range
    For Each c In myrange
        If Not c.EntireRow.Hidden And c.Value ="" Then
            CountBlankVisible = CountBlankVisible + 1
        End If
    Next
End Function