Counting results without an empty cell without cycling in Excel VBA - for example, with special cells

Question

Here is the code I'm trying to count in VBA, hoping to return the returned variable count "3" from "FormulaResultCount". Why can't I assume that it is explicitly returned by the formulas inside each cell; from a gray window (see photo below)?

Sub countNonBlanks() Worksheets("Sheet1").Select Range("C:C").Select FormulaResultCount = Selection.SpecialCells(xlCellTypeFormulas).Count 'SpecialCells options from MSFT ' xlCellTypeAllFormatConditions. Cells of any format -4172 ' xlCellTypeAllValidation. Cells having validation criteria -4174 ' xlCellTypeBlanks. Empty cells 4 ' xlCellTypeComments. Cells containing notes -4144 ' xlCellTypeConstants. Cells containing constants 2 ' xlCellTypeFormulas. Cells containing formulas -4123 ' xlCellTypeLastCell. The last cell in the used range 11 ' xlCellTypeSameFormatConditions. Cells having the same format -4173 ' xlCellTypeSameValidation. Cells having the same validation -4175 ' xlCellTypeVisible. All visible cells ' End Sub 

See the formula as a reference:

Note. Since I will have more cells during dynamic work, loops are more likely to slow down the process. In addition, I tried using CountA with no result.

Answer 1

xlCellTypeFormulas. Cells Containing Formulas -4123

This does not return the cell based on their values, but if they have any formula or not. According to your sheet you should get 5

Also, PLEASE PLEASE .Select INTERESTING .Select READ

Your code can also be written as

FormulaResultCount = Worksheets("Sheet1").Columns(3).SpecialCells(xlCellTypeFormulas).Count

Another tip . When using SpecialCells use appropriate error handling so that if there are no cells that meet the SpecialCells criteria, your code will not be interrupted. See this example.

 Sub Sample() Dim ws As Worksheet Dim Rng As Range Set ws = ThisWorkbook.Sheets("Sheet1") With ws On Error Resume Next Set Rng = .Columns(3).SpecialCells(xlCellTypeFormulas) If Err <> 0 Then MsgBox "No Cells with formulas were found" Exit Sub End If On Error GoTo 0 End With FormulaResultCount = Rng.Count Debug.Print FormulaResultCount End Sub 

FOLLOWUP From Comments

 Sub Sample() Dim ws As Worksheet Dim lRow As Long Set ws = ThisWorkbook.Sheets("Sheet1") With ws lRow = .Range("A" & .Rows.Count).End(xlUp).Row Debug.Print Evaluate("=COUNTA(C1:C" & lRow & _ ")-COUNTIF(C1:C" & lRow & ","""")") End With End Sub 

Answer 2

What you can really wish for:

 FormulaResultCount = Evaluate("CountA(C:C)") 

I just found out about the assessment team. It's amazing!

And that gives you 3 :)

Answer 3

Perhaps it:

 FormulaResultCount = WorksheetFunction.CountIf(Range("C:C"), "?*") 

So, counting all the cells in the range starting with any character?

Answer 4

You can do this without VBA using only formulas.

 =ROWS(range)*COLUMNS(range)-COUNTBLANK(range) 

If you are trying to do this in VBA, you can use this:

 Function non_blank_cell_results_count(r As Range) As Long non_blank_cell_results_count = r.Cells.Count - WorksheetFunction.CountBlank(r) End Function