Login | Register   
RSS Feed
Download our iPhone app
Browse DevX
Sign up for e-mail newsletters from DevX

By submitting your information, you agree that devx.com may send you DevX offers via email, phone and text message, as well as email offers about other products and services that DevX believes may be of interest to you. DevX will process your information in accordance with the Quinstreet Privacy Policy.

Tip of the Day
Language: VBA
Expertise: Intermediate
Sep 19, 2006



Application Security Testing: An Integral Part of DevOps

Count the Number of Cells Containing a Specified String in a Worksheet

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
                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
Alistair Evans
Comment and Contribute






(Maximum characters: 1200). You have 1200 characters left.



We have made updates to our Privacy Policy to reflect the implementation of the General Data Protection Regulation.
Thanks for your registration, follow us on our social networks to keep up-to-date