Counting non blank cell results without looping in Excel VBA -- e.g., with Specialcells

Question

Here is the code I'm trying to count with in VBA, hoping to return a count return variable of "3" from 'FormulaResultCount'. Why can't I count what is visibly returned by the formulas within each cell; from the grey box (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 formula as reference:

enter image description here

Note: Since I will have many more cells when working dynamically, loops will likely slow the process down too much. Also, I tried using CountA without result.

if D is showing the formula you have in C, I'd expect your answer to be 5. They are still formulas even if they result in "" as an answer. — Derek, Oct 18 '13 at 01:32
@DougGlancy A value of 5 gets assigned, counting each cell with a formula. Instead I would like to be assigned a 3 for the cells triggered by column B. — Bob Hopez, Oct 18 '13 at 02:30

score 4 · Answer 1 · answered Apr 24 '14 at 10:20

4

Maybe this:

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

Thus counting all cells in range that start with any character?

answered Apr 24 '14 at 10:20

user3568343

41
1

score 3 · Accepted Answer · edited May 23 '17 at 12:13

3

xlCellTypeFormulas. Cells containing formulas -4123

This would not return the cell based on their values but if they have any formula or not. As per your worksheet, you should get 5

Also, PLEASE PLEASE do not use .Select INTERESTING 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 which match the SpecialCells criteria, your code won't break. 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

edited May 23 '17 at 12:13

Community

1
1

answered Oct 18 '13 at 01:33

Siddharth Rout

147,039
17
206
250

Very interesting approach, but it still returns a 5, when I'm looking to return a 3 -- i.e., for each value returned by if() functions. Is there an alt to 'xlCellTypeFormulas' in the list above which might work with the code you shared? – Bob Hopez Oct 18 '13 at 02:44
1

@BobHopez: I have updated the post. See `FOLLOWUP From Comments` – Siddharth Rout Oct 18 '13 at 03:00
Good attempt; still returns a 5. Want to just search column C--not sure why the FOLLOWUP uses "A"--regardless, "A"/"C" in place still returns 5 and not 3. – Bob Hopez Oct 18 '13 at 03:08
Scratch that @SiddharthRout!! I checked the immediate window, and the three popped up...many of them. – Bob Hopez Oct 18 '13 at 03:11
Note to readers: `Application.SendKeys "^g ^a {DEL}"` clears the immediate window. – Bob Hopez Oct 18 '13 at 03:13

score 3 · Answer 3 · answered Oct 18 '13 at 01:41

3

What you really might want is:

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

I just learnt about the evaluate command. It's awesome!

And it gives you 3 :)

answered Oct 18 '13 at 01:41

Derek

7,615
5
33
58

@Derek Might be a good alternative, but it still returns a 5, as there are 5 functions in the C column. I would like to return a 3, for each value triggered as visible from the if() within the cells. – Bob Hopez Oct 18 '13 at 02:38

Will Ediger · Answer 4 · 2015-05-01T04:15:29.927

1

You can do this without VBA, using only formulas.

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

If you're 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

edited May 01 '15 at 04:15

answered May 01 '14 at 18:38

Will Ediger

893
9
17

Counting non blank cell results without looping in Excel VBA -- e.g., with Specialcells

4 Answers4

Linked