CODEWITHSUNDEEP
excelvba
Anthony Bird
Anthony Bird

Reputation: 261

Counting Visible Blank cells in a row

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?

Upvotes: 2

Views: 707

Answers (2)

Gary's Student
Gary's Student

Reputation: 96753

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

Upvotes: 1

Dmitry Pavliv
Dmitry Pavliv

Reputation: 35853

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

Upvotes: 1

Related Questions