Count Number of Cells on Conditions

Question

I need to count the number of blank cells, and cells that contain a number less than 60 in column O. Every time I try CountIf(Range("O:O"), "") I get a value of 1048539. I'm expecting way less than that. Am I going about this right? I can count the number of cells that are less than 60 perfectly fine.

The blank sells are set by an equation that makes them equal "" if they don't meet a condition.

I can now find out how many rows my data has, but I still cannot figure out how to count the number of empty cells.

Here is what I am trying.

Sub count()
   Dim blanks As Long
   Dim totalRows As Long
   totalRows = Application.WorksheetFunction.CountA(Range("O:O")) - 1
   For i = 1 To totalRows
        If (Application.WorksheetFunction.isBlank(Cells(i, "O"))) Then
            blanks = blanks + 1
        End If
   Next i
End Sub

I get the following error

Object doesn't support this property or method

I think it has to do with Cells() but I'm not sure what else I can use.

My table is read from a database, so it'll grow in size and shrink. There is no way of telling how many rows are created. This is the only way that I can think of. — , Jun 24 '16 at 20:05
`no way of telling how many rows are created` ahhh, but there is ~_^ — findwindow, Jun 24 '16 at 20:06
but if you count the entire column any that are below the table will be counted also. You need to use a method to find the last row. See [HERE](http://stackoverflow.com/questions/11169445/error-in-finding-last-used-cell-in-vba) — Scott Craner, Jun 24 '16 at 20:08
Busy day. Just taking a quick break to refresh the mind. @findwindow — Scott Craner, Jun 24 '16 at 20:11

score 0 · Answer 1 · answered Jun 24 '16 at 21:58

In a rush right now, but something like this may help:

Sub CountCells()

   Dim TotalFilledRows As Long
   Dim TotalRows As Long
   Dim EmptyCells As Long
   Dim CellsWithNumbers As Long

   TotalFilledRows = Application.WorksheetFunction.CountA(Range("O:O"))
   TotalRows = Cells(Rows.count, 15).End(xlUp).Row

   For i = 1 To TotalRows
        If IsEmpty(Cells(i, 15)) Then
            EmptyCells = EmptyCells + 1
        End If

        If Application.WorksheetFunction.IsNumber(Cells(i, 15)) Then
            CellsWithNumbers = CellsWithNumbers + 1
        End If
   Next i

   MsgBox "Total: " & TotalRows & "; Filled: " & TotalFilledRows & "; Empty cells: " & EmptyCells & "; Cells with numbers: " & CellsWithNumbers
End Sub

@pcw Yes, I know. I wanted to count total, empty and with numbers. Then make a calculation. I like your code, specially because is loopless. — CMArg, Jun 25 '16 at 01:08

PaichengWu · Accepted Answer · 2016-06-24T23:38:26.327

0

Dim blanks As Long
with range(range("o1"), range("o" & rows.count).end(xlup))
    blanks = application.countif(.cells,"") - .specialcells(xlcelltypeblanks).count
end with

Because:

countif(.cells,"") will return the number of "" and empty cells.

.specialcells(xlcelltypeblanks).count will return the number of empty cells.

edited Jun 24 '16 at 23:38

answered Jun 24 '16 at 23:21

PaichengWu

2,649
1
14
28

1

funny facts: `Application.CountBlank([range])` will count empty cells and cells with empty strings while `Application.CountIf([range], "=")` will only count cells which are emtpy... this way `Application.CountBlank(Range("O:O")) - Application.CountIf(Range("O:O"), "=")` is also an option ;) – Dirk Reichel Jun 25 '16 at 02:23
@DirkReichel, Thank you. I always feel confused about blank, empty and "" string. I do not know what Microsoft define **Blank**. `CountBlank()` returns th number of empty and "", but in VBA `Specialcells(xlCellTypeBlanks)` returns the number of empty only. – PaichengWu Jun 25 '16 at 04:19
Both Answers work, but I'm using this one because it's loopless – Jun 27 '16 at 12:33

Count Number of Cells on Conditions

2 Answers2