Filtering in VBA

[JoeS01]

I have a large 2 dimensional array set up in VBA, and wish to count the number of items where the value is below a certain figure, then move on.

Rather than going through each cell, is there any quick way I can filter the array and use the criteria as CellValue<=1 and then count the number of cells meeting this criteria.

All help greatly appreciated

 

[NateO]

Hey Joe,



Er, sorry. Right, probably need more detail to answer this.

You have a range of data or a fundamental data type burned into memory as an array?

Filtering a Range is one thing. Filtering in VBA is another, the Filter Function only works on 1-d arrays, so if you're dead set on working with a 2-d fundamental data type, you might need to loop through the array.

 

[Legacy 14611]

How quick do you want?
Sub testdata genenerates half a million random entries between zero and 2.
Sub countem counts those less than 1 (or whatever you specify) in about 1.7 secs on my machine.
Is this something like you envisage?

Sub testdata()
n = 5000: m = 100
ReDim k(n - 1, m - 1)
    For i = 1 To n
        For j = 1 To m
            k(i - 1, j - 1) = Rnd() * 2
    Next j, i
Range([a1], Cells(n, m)) = k
End Sub

Sub countem()
x = Timer
For Each v In [a1].CurrentRegion
    If v <= 1 Then p = p + 1
Next
tm = Timer - x
MsgBox p & " entries less then 1" & "  Time taken " & tm & " secs"
End Sub

 

[NateO]

If you have an Array of data, loop through that, this is much faster than looping through a Range.

And if you have a populated Range, use CountIf(), e.g.,

=COUNTIF(A1:CV5000,"<1")

I.e., no need to loop in this case.

 

[JoeS01]

With thanks

Thanks rugila and NateO. ruglia' contribution is the code I was looking for - pretty quick for me, many thanks to you both.

 

[NateO]

Wait, no, don't do that! There was a reason for my follow-up, eh.

Seriously, why not just iterate through the array? Really? It's much, much faster. Look at how fast this is for 2,000,000 iterations:

Sub foobar()
Dim lngArr() As Long, i As Long, j As Long
Dim tstTime As Date, myCnt As Long, lpCnt As Long

Let tstTime = Now

ReDim lngArr(1 To 50000, 1 To 20)

For i = LBound(lngArr, 1) To UBound(lngArr, 1)
    For j = LBound(lngArr, 2) To UBound(lngArr, 2)
        Let lngArr(i, j) = Int(Rnd - 10000)
    Next j
Next i

For i = LBound(lngArr, 1) To UBound(lngArr, 1)
    For j = LBound(lngArr, 2) To UBound(lngArr, 2)
        If lngArr(i, j) < 1 Then Let myCnt = myCnt + 1
        Let lpCnt = lpCnt + 1
    Next j
Next i

Debug.Print "Count less than 1: " & myCnt, _
    "Time Taken: " & Now - tstTime, "Loop Count: " & lpCnt * 2

End Sub

It barely even registers!!

And at the very least, declare your variables! Use 'Option Explicit' when you write VB[A] and compile. This will save you headaches down the road, trust me.

Also, you do want to declare both your Upper and Lower boundaries on arrays. Excel and VB are at odds with one another, natively, here. Excel tends to be a 1-based Application, natively, while VB tends to be a 0-based Application, natively.

Take the time to do this right. At the very least, it self-documents your intent and code.

 

[JoeS01]

with thanks

thanks for taking the time to do this, Nate - it is a very useful lesson. I shall implement both methods and see how they go.

 

[jindon]

Joe
try

Sub test()
With Range("a1:z20000")
    .Formula = "=rand()*2"
    .Value = .Value
    a = .Value
End With
x = Timer
For Each e In a
    If e <= 1 Then n = n + 1
Next
y = Timer - x
MsgBox n & " : took " & y
End Sub

 

[JoeS01]

for jindon

Thanks for your post, jindon - I am afraid tht I cannot understand the code, especially the "a".

I have had a go at running it, after dimensioning the variables, but get a runtime error #91 Object variable or block variable not set.

Can you please have a look and advise where I have gone wrong?

I would really appreciate it if you could explain the lines of code with the << pointer

Sub test()
Dim a As Range

Dim x As Double
Dim e As Range
Dim n As Long
Dim y As Double

With Range("a1:z20000")
    .Formula = "=rand()*2"
    .Value = .Value      <<<<<
    a = .Value              <<<<<
End With

x = Timer
For Each e In a          <<<<<<
    If e <= 1 Then n = n + 1
Next
y = Timer - x
MsgBox n & " : took " & y
End Sub

 

[NateO]

a is a Variant Array and e is a Variant. Another reason to declare your variables... So that people can [quickly] determine what you're doing...

So back to what I originally asked... Is this data in a Range or only in an Array? If it's in a Range, use Countif(), eh. E.g.,

Sub foobar()
Dim tstTime As Date
Let tstTime = Now
With Range("A1:Z50000")
    .Formula = "=rand()*-10000"
    .Value = .Value
End With

Debug.Print "Count less than 1: " & WorksheetFunction.CountIf(Range("A1:Z50000"), "<1"), _
    "Time Taken: " & Now - tstTime, "Loop Count: 0"

End Sub

Otherwise, iterate through the array.