Count number of populated cells in range

Question

How do I count the number of populated cells (with text/number or combinations of text+number)?

I tried =countif(A2:A2000,1=1) (with a general criteria, e.g. 1=1 always) but shows zero (0) for a text column.

The formula is `=COUNTA(A2:A2000) `: non-blank cells are counted — Jüri Ruut, Jan 09 '13 at 20:12
@juriruut you should post that as the answer so you might get some imaginary internet points! — scott, Jan 09 '13 at 20:35

score 11 · Answer 1 · edited Oct 23 '18 at 20:43

11

I think for the answer above, it should rather be

Application.WorksheetFunction.CountA(Range("A2:A2000"))

I have always faced problems while not mentioning Range, cause it considers the whole range to be as a single unit.

edited Oct 23 '18 at 20:43

Lance Roberts

answered Oct 27 '15 at 07:07

Varun Rajan

score 5 · Accepted Answer · answered Jan 09 '13 at 20:40

5

The formula is =COUNTA(A2:A2000) : non-blank cells are counted.

answered Jan 09 '13 at 20:40

Jüri Ruut

score 5 · Answer 3 · answered Apr 26 '13 at 22:44

5

In VBA it's WorksheetFunction.CountA("A2:A2000")

answered Apr 26 '13 at 22:44

KurtisT

score 0 · Answer 4 · answered Apr 15 '16 at 07:26

0

Dim Coloumncount As Integer Coloumncount = Application.WorksheetFunction.CountA([D1:D100])'count how many filled cell in coloumn "D"

answered Apr 15 '16 at 07:26

user2965711

4 Answers4