Excel VBA: Function to Count Non Empty Element of an Array

Question

I am looking for a way to count non-empty element in a 2 dimensions array.

I have read a solution here VBA count non empty elements of array. However I think there may be a better way or function to perform it.

To count non-empty cells in a range (A1:J10), we use this in Excel VBA

    k = Application.WorksheetFunction.CountA(Range(Cells(1, 1), Cells(10, 10)))

I am looking for similar function to count non-empty elements in an array. For instance, I tried this

    k = Application.WorksheetFunction.CountA(Array(1,1),Array(10,10))

But it does not work. Is there such function to count non-empty element in array?

I need to count this because I would like to delete the entire rows in an array when all elements of the same rows in an array are empty.

Thanks.

Is there any way you could dump the array values into some ranges and then use the CountA function? Perhaps create a sheet, dump the arrays into ranges, use the function, and then delete the sheet? — DeanBDean, Mar 5 at 5:45
Hi Dean, To count it, yes, your method is workable. But my full procedure is more complicated. I am capture the selected cells into array and process the string. I need to make sure there is no empty cells before I process these string. I was hoping to use the shortcut where I can just place the entire range of cells in one area into array. Like: Array = Range("A2:F10000").value and deal with the empty cells later in the array. But it seem it will be easier to deal with the empty cells before the entire range place into array. Thanks. — Chen, Mar 6 at 13:14

Siddharth Rout · Accepted Answer · 2014-03-05 05:22:12Z

up vote 1 down vote accepted

AFAIK There's no built in function for your request.

Siddharth Rout
57.2k84870

answered Mar 5 at 4:38

GopinathR
496

+ 1 True. AFAIAK, There's no built in function. – Siddharth Rout Mar 5 at 5:22

Thanks, GopinathR. Thanks, Siddharth. I think you are right. – Chen Mar 6 at 13:04

1 Answer