VBA count non empty elements of array

Question

Noob question: I want to count the non empty elements of an array?

My attempt:

Dim Arr(1 To 15) As Double
'populating some of the elements of Arr
'...

Dim nonEmptyElements As Integer, i As Integer
nonEmptyElements = 0: i = 0
For i = LBound(Arr) To UBound(Arr)
    If Not Arr(i) = "" Then
        nonEmptyElements = nonEmptyElements + 1
    End If
Next

With this program I get the error: Type mismatch on If statement.

If try to change the if condition to If Not IsEmpty(Arr(i)) Then and i get nonEmptyElements = 15 as a result.

Any suggestions on how to complete the code?

You're defining Arr as Double so can't contain "empty" elements like it could if it was variant. They're always a number. So I think the program is working right, just not doing what you're looking for. Say more about what your goal is? — asantaballa
– asantaballa, Commented Jul 26, 2013 at 9:49
Can the array contain 0 as an element ? If not, write the if statement as below: If Arr(i) <> 0 Then — rags
– rags, Commented Jul 26, 2013 at 9:53
If you wanted to treat it that way. May depend on your application. may be ok for, say, lottery system, where lottery number is never zero. For accounting system where a budget amount could be zero you would need to use variant type to allow "true blanks". — asantaballa
– asantaballa, Commented Jul 26, 2013 at 9:57

Ashneil Roy · Accepted Answer · 2013-07-26 15:03:37Z

2

    Dim Arr(0 To 15) As Double
    Arr(6) = 1.2
    Arr(3) = 7
    Dim nonEmptyElements As Integer, i As Integer
    nonEmptyElements = 0 : i = 0
    For i = LBound(Arr) To UBound(Arr)
        If Not Arr(i) = 0 Then
            nonEmptyElements = nonEmptyElements + 1
        End If
    Next

A double value by default is 0.0, so check if:

Arr(i) = 0

answered Jul 26, 2013 at 15:03

Ashneil Roy

1545 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

alexkovelsky · Accepted Answer · 2016-05-23 09:42:20Z

1

Application.CountA(myarray)

CountA is a worksheet function for counting non-empty values.

Applies only to VBA6, does not work in VBA7.

edited May 23, 2016 at 9:42

answered Mar 12, 2014 at 14:38

alexkovelsky

4,2582 gold badges31 silver badges22 bronze badges

1 Comment

alexkovelsky Over a year ago

@ExcelDevelopers you're right, but note that it worked in VBA6. CountA behavior is different in VBA6 and VBA7.

Collectives™ on Stack Overflow

VBA count non empty elements of array

2 Answers 2

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related