Login | Register   
Twitter
RSS Feed
Download our iPhone app
TODAY'S HEADLINES  |   ARTICLE ARCHIVE  |   FORUMS  |   TIP BANK
Browse DevX
Sign up for e-mail newsletters from DevX


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

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

 

 

 

 

 


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

 

 

Sitemap