How to query a range

[oaishm]

I have a range called movie with field names in the first row. It looks like this:

movienum title rating
1 batman R
2 superman R
3 wonderwoman PG

How can I return an array of only the records that will fulfill some criteria. If this were sql it would look like

select * from movie where rating = "PG"

 

[Legacy 68668]

You are trying to resizing the 1st dimention, but you can only resize the last dimention of the array.
try

Function filterarray(frange As Range, filtercolumn, criteria As String)
Dim a, i As Long, ii As Long, n As Long, result() As Variant
a = frange.Value
For i = 1 To UBound(a, 1)
    If a(i, filtercolumn) = criteria Then
        n = n + 1
        ReDim Preserve result(1 To UBound(a, 2), 1 To n)
        For ii = 1 To UBound(a, 2)
            result(ii, n) = a(i, ii)
        Next
    End If
Next
filterarray = Application.Transpose(result)
End Function

 

[oaishm]

You can only resize the last dimension of an array? Why is that. See, this is stuff that isn't documented anywhere. Then you have to transpose in order to filter. That's crazy. Alas. Thanks for the help. This has been killing me

 

[Legacy 68668]

If you need to deal with the array, you should understand the basic nature of the array, otherwise it will always put you in a trouble.