dcsimg
Login | Register   
LinkedIn
Google+
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

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

WEBINAR:

On-Demand

Building the Right Environment to Support AI, Machine Learning and Deep Learning


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
Thanks for your registration, follow us on our social networks to keep up-to-date