Empty array element in Excel worksheet range showing ISBLANK = FALSE

Question

I am writing an array to a range in a Column. The array consists of numbers and blanks. When stored in the range, the numbers are displayed in scientific notation. To ensure that they are displayed as text, I have formatted the Column using the below code:

dws.Columns("m:m").NumberFormat = "@"

Now, the numbers are correctly displayed and although the blank cells have len = 0, ISBLANK = FALSE. As a result, I am unable to use the COUNTIF function to count the cells with numbers.

(If the Numberformat = "@" code is removed then the COUNTIF works correctly except that the numbers are not displayed as required.)

Looking forward to some quick solution/workaround to the problem.

Answer 1

As you have only blanks and/or values that can interpreted as numeric you might profit from just using the COUNTA() function which omits counting blanks despite prior `NumberFormat = "@" applied to the referenced (column) range:

• in cell: =COUNTA(M:M)
• via VBA: WorksheetFunction.CountA(dws.Columns("M:M"))

Note: this assumes the columns reference M:M within a worksheet (i.e. not referring to a table for instance).