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.

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 = Workbooks(WB).Worksheets(1).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)
'Rett = WorksheetFunction.CountA(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
End Function

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

Views 1741 Downloads 872


ANmarAmdeen
592
Components VBA-Excel
Revisions

v2.0