VBA Count Values in Array

Question

I've read the post on this VBA problem, but my VBA script is still not working.

Public Sub Test()

Dim arrNames As Variant                     'Declare array named "arrNames"
arrNames = Sheet1.Range("F2:F1000")         'Array filled with column F
intN = Application.CountIf(arrNames, "*")   'does not work intent: count cells w/info
'intN = Application.CountA(arrNames)        'works but MsgBox displays value of 999
MsgBox (intN)

End Sub

How do I get the number of cells in my array containing any value?

EDITED version after help

Public Sub Test()

Dim arrNames As Variant                       'Declare array named "arrNames"
Dim i As Long

arrNames = Sheet1.Range("F2:F1000")           'Array filled with column F

For i = LBound(arrNames) To UBound(arrNames)
    If (arrNames(i,1) = "") Then
        EmptyCounter = EmptyCounter + 1
    End If
Next i

End Sub

Can you not use the simplest approach like MsgBox Application.WorksheetFunction.CountBlank(Range("F2:F1000"))?? — user2140173
– user2140173, Commented Jul 4, 2014 at 7:25
No and Yes. arrNames needs to be changed on Line 9 after the If statement as such: If arrNames(i,1) otherwise an error subscript out of range results. — Greg
– Greg, Commented Jul 10, 2014 at 17:08

hnk · Accepted Answer · 2014-07-03 21:05:35Z

2

There is no direct way to do it, as far as I understand. But you could run a simple loop to check if the values are equal to "" assuming string data.

For e.g.

For i = LBound(ArrayName) to UBound(ArrayName)
    If (ArrayName(i) = "") Then
        EmptyCounter = EmptyCounter + 1
    End If
Next i

If it's numeric or other type of data, you may try variations of the above loop using functions such as IsEmpty(VariableName) etc.

answered Jul 3, 2014 at 21:05

hnk

2,2221 gold badge15 silver badges18 bronze badges

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

8 Comments

Greg Over a year ago

It usally just a string, e.g mixed data like S12-9

Greg Over a year ago

If (arrNames(i) = "") Then 'Script hangs on this line

hnk Over a year ago

what do you mean 'hangs'? What's the error that pops up?

Greg Over a year ago

Run Time Error '9':Subscript out of Range

hnk Over a year ago

That is very very odd, if it's going from LBound to Ubound, it's almost impossible for that. Is i declared as Long? or Integer? Please declare it as Long and see again.

|

crazylane · Accepted Answer · 2014-07-03 20:48:13Z

1

You can try this:

intN = Worksheets("Sheet1").Range("F2:F1000").Cells.SpecialCells(xlCellTypeConstants).Count
MsgBox intN

100% it works.

answered Jul 3, 2014 at 20:48

crazylane

627 bronze badges

6 Comments

Greg Over a year ago

This does work, however I need "count" for an array as my arrays are manipulated in the script.

crazylane Over a year ago

I'm nit sure I get you right on this one. But if I do you can try somthing like this: intN = arrNames.Cells.SpecialCells(xlCellTypeConstants).Count - I'm assuming that you had declared arrNames as Range before. Does that help?

Greg Over a year ago

What I meant was your answer really evaluates the values that will be placed into the array, not the array iteself. But there are times I will need to count the array after I have "sorted", removed duplicates etc from the array. In that case the intN = Worksheets... would not work.

crazylane Over a year ago

ok, still not giving up on this one - you can re-define your range in terms of rows like this:

Dim TheLastRow As Long     TheLastRow = Worksheets("Sheet1").Range("F2:F1000").Cells.SpecialCells(xlCellTypeLastCell).Row     MsgBox (TheLastRow)

- that one finds the last non-empty row. then you can fire up the code I proposed in my first answer.

Greg Over a year ago

This makes sense and intN will give you an interger value. However how do I enter that last row value in setting the array range? The following does not work. arrNames = Sheet1.Range("F2":intN)

|

Collectives™ on Stack Overflow

VBA Count Values in Array

2 Answers 2

8 Comments

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

8 Comments

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related