devxlogo

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

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 ExplicitDim max_column_number As LongDim max_row_number As LongDim counter As LongDim x As LongDim y As LongDim checkstring As StringFunction 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 = counterEnd Function

devx-admin

Share the Post: