This small function counts the number of cells in a worksheet that contain a specified string and outputs a total. Suppose you set the string to
= "". The function counts the number of cells in the sheet that contain anything—you simply insert it into a module:
Option Explicit
Dim max_column_number As Long
Dim max_row_number As Long
Dim counter As Long
Dim x As Long
Dim y As Long
Dim checkstring As String
Function GetNotBlankCellCount(max_column_number, max_row_number, checkstring)
counter = 0
For x = 1 To max_column_number 'this is equal to the maximum number of columns you want to check
For y = 1 To max_row_number 'this is equal to the maximum number of rows you want to check
If checkstring = "" Then
If Not Sheet1.Cells(x, y) = "" Then 'change sheet1 to your sheet name
counter = counter + 1
End If
Else
If Sheet1.Cells(x, y) = checkstring Then 'change sheet1 to your sheet name
counter = counter + 1
End If
End If
Next y
Next x
GetNotBlankCellCount = counter
End Function
