excel - count blank cells (Translating A to 1, D to 4.etc)

I want to supply a number (ie. 4 which represents the 4th column - D) and then have a msgbox return the number of non blanks in that column

For example below there are 5 blanks in column D:

col A     col B      col C     col D     col E
------   -------     ----     -------    ------
                               gh


                               2h



                               34 

code so far:

Sub Tester()
CountBlanks 4
End Sub

Sub CountBlanks(ByVal colNum)
columnLetter = ??
columnCount = ??
MsgBox "Count of column: " & columnLette开发者_如何转开发r & " is " & columnCount 
End Sub

Expected result:

"Count of column: D is 5" 

The answer depends on what you actually want to count:

1. All blank cells in a column with respect to the entire table.
2. All blank cells up to the last used cell within the specific column itself.

Most people are trying to achieve the first option which is a little tricky. For counting blanks the specialcells function is the way to go. However as specialcells uses the sheets usedrange it can become unreliable as excel is known to keep track of the usedrange badly. So first we must call the usedrange function to reset it.

Function CountBlanks(colNum As Long)    
    Dim sColumn As String
    Dim lBlanks As Long

    sColumn = Split(Columns(colNum).Address(ColumnAbsolute:=False), ":")(0)    

    ActiveSheet.UsedRange
    lBlanks = ActiveSheet.Columns(colNum).SpecialCells(xlCellTypeBlanks).Count

    MsgBox "Count of column: " & sColumn & " is " & lBlanks
End Function

If you were after option 2 then it is just a case of getting the last cell in the column and counting with specialcells.

Function CountBlanks(colNum As Long)    
    Dim sColumn As String
    Dim lBlanks As Long

    sColumn = Split(Columns(colNum).Address(ColumnAbsolute:=False), ":")(0)    

    lBlanks = Range(Cells(1, colNum), Cells(Rows.Count, colNum).End(xlUp)).SpecialCells(xlCellTypeBlanks).Count        

    MsgBox "Count of column: " & sColumn & " is " & lBlanks
End Function

EDIT

Jmax, the code is actually correct. The call to reset the used range has to be on its own. On the next line when calling specialcells you don't need to specify usedrange.

Its hard to explain but try this: Comment out the ActiveSheet.UsedRange line. With your test data add a value to a cell further down the page from the table and run the code. It will return the count of blanks up to this newly added cell. Now delete the entire row of the cell you added and re-run the code. It will still return the same count because excel has not reset its inbuilt counter. Now uncomment the line Activesheet.Usedrange, run the code and you will get the correct result.

Here is a solution with xlCellTypeBlanks (thanks to brettdj for the column letter code (didn't want to search for it)

Sub CountBlanks(ByVal colNum)
Dim iRow As Long
Dim columnLetter As String
columnLetter = Split(Columns(colNum).Address(False, False), ":")(0)
iRow = Cells(Rows.Count, colNum).End(xlUp).Row
columnCount = Range(columnLetter & "1:" & columnLetter & iRow).SpecialCells(xlCellTypeBlanks).Count
MsgBox "Count of column: " & columnLetter & " is " & columnCount
End Sub

Sub Tester()
CountBlanks 1
End Sub

You can use the worksheet function COUNTA to so this, plus manipulate the column address with Split to get the aplhabetic columm letter

Note that any "" results will be counted

Function CountBlanks(colNum As Long)
CountBlanks = "Count of column: " & Split(Columns(colNum).Address(False, False), ":")(0) & " is " & Application.WorksheetFunction.CountA(Columns(colNum))
End Function

Use a loop and increment columnCount when the cell is not blank.

How do you know when you've reached the last row?