CountColumnCells + CountColumnCells_Criteria

Counts how many cells in a column in any sheet, any workbook using COUNTIF function, along with version that searches for certain criteria.

CodeFunctionName
What is this?

Public

Tested

Original Work
Function CountColumnCells(ColumnName, Optional WB = "This", Optional Shee = "Active", Optional StartFromRow = 1)
    CountColumnCells = CountColumnCells_Criteria(ColumnName, "", WB, Shee, StartFromRow)
End Function

Function CountColumnCells_Criteria(ColumnName, Criteria, Optional WB = "This", Optional Shee = "Active", Optional StartFromRow = 1)
    ' Condition = "#" means only numbers
    '            = "" means all
    '            = " >0" numbers greator than 0
    '
    Dim SearchRR As Range
    If WB = "This" Then WB = ThisWorkbook.Name
    If WB = "Active" Then WB = ActiveWorkbook.Name
    If Shee = "Active" Then Shee = ActiveSheet.Name
    If ColumnName = "" Then ColumnName = "A"
    Set SearchRR = Workbooks(WB).Worksheets(Shee).Range(ColumnName & 1).EntireColumn
    If StartFromRow > 1 Then
        LastR = Range("A1").EntireColumn.Rows.Count
        Set SearchRR = Workbooks(WB).Worksheets(Shee).Range(ColumnName & StartFromRow, ColumnName & LastR)
    End If
    If Criteria = "#" Then
        Rett = WorksheetFunction.Count(SearchRR)
    ElseIf Criteria = "" Then
        Rett = WorksheetFunction.CountA(SearchRR)
    Else
        Rett = WorksheetFunction.CountIf(SearchRR, Criteria)
    End If
    CountColumnCells_Criteria = Rett
    Set SearchRR = Nothing
End Function

ColumnName, Criteria, WB, Shee, StartFromRow
or
ColumnName, WB, Shee, StartFromRow

Views 4,061

Downloads 1,556

CodeID
DB ID

ANmarAmdeen
618
Revisions

v2.0

Monday
April
29
2024