Excel UDF Filter Range

Question

I have a function that takes a range of values as input (just a column) as well as some threshold. I would like to return a range that is filtered to include all values from the original range that are greater than the threshold. I have the following code:

Public Function FilterGreaterThan(Rng As Range, Limit As Double) As Range

Dim Cell As Range
Dim ResultRange As Range

For Each Cell In Rng
    If Abs(Cell.Value) >= Limit Then
        If ResultRange Is Nothing Then
            Set ResultRange = Cell
        Else
            Set ResultRange = Union(ResultRange, Cell)
        End If
    End If    
Next
Set FilterGreaterThan = ResultRange
End Function

The issue is that once a number is below the threshold, other numbers after that one that are above the threshold do not get added to the range.

For example:

Threshold - 2

Numbers -

3
4
1
5

It will loop through adding 3 and 4 but 5 will not be added. I end up getting a #value error. But I get no error and it works fine if I only enter the range - 3, 4 or the range - 3, 4, 1.

Hi, you're function works fine if you put it inside another function like =SUM(FilterGreaterThan(A1:A7,2)). The problem I think you're having is that you're putting =FilterGreaterThan(A1:A7,2) in a cell and when it returns a multi-area range, you get a #VALUE! error. Even still, I don't understand the result that comes back from =FilterGreaterThan(A1:A7,2) anyway since you are just getting back a (what seems to be) random number from the range. — Joseph, Sep 12 '12 at 20:39
If you add `Debug.Print FilterGreaterThan.Address` just before End Function, you can see the range address of the function. It looks like UDF's perhaps don't like non-contiguous ranges. — , Sep 12 '12 at 21:28

score 2 · Answer 1 · 2012-09-12T22:10:18.540

2

It's looks like the UDF doesn't like non-contiguous ranges being written back to an array.

One way around it is to re-write the UDF like below. It assumes the output array is only in column but does allow multiple column input.

Option Explicit

Public Function FilterGreaterThan(Rng As Range, Limit As Double) As Variant

Dim Cell As Range
Dim WriteArray() As Variant
Dim i As Long
Dim cellVal As Variant
Dim CountLimit As Long

CountLimit = WorksheetFunction.CountIf(Rng, ">=" & Limit)
ReDim WriteArray(1 To CountLimit, 1 To 1) 'change if more than 1 column
For Each Cell In Rng

    cellVal = Cell.Value
    If Abs(cellVal) >= Limit Then
            i = i + 1 'change if more than 1 column
            WriteArray(i, 1) = cellVal 'change if more than 1 column
    End If
Next
FilterGreaterThan = WriteArray
End Function

edited Sep 12 '12 at 22:10

answered Sep 12 '12 at 21:57

+1 although if `CountLimit` is 0 then it will just return `#VALUE!` (instead of `#N/A`) if called as a UDF. If called from VBA then you would get a "Subscript out of range" error if `CountLimit` is 0 – barrowc Sep 12 '12 at 22:43
Your right, there's no error handling and assumes CountLimit>0. – Sep 12 '12 at 22:45
I just looked at the code. It seems that the CountLimit = WorksheetFunction.CountIf(Rng, ">=" & Limit) doesn't take into account the absolute value consideration. Not sure where to add that though – user1666842 Sep 12 '12 at 23:45
I was trying to be sneaky and get the value of the array but forgot about ABS! :) – Sep 12 '12 at 23:58
That ABS is a slippery little sucker :) – user1666842 Sep 13 '12 at 04:21

barrowc · Answer 2 · 2012-09-13T00:33:19.473

ooo got there first but I've typed it out now so I may as well post it. This version will return as a column vector of the correct size.

If nothing matches then #N/A is returned in a 1 by 1 array (this is consistent with the normal behaviour of an array function when there are insufficient values to fill the array)

edit2: updated function thanks to comments from ooo

Public Function FilterGreaterThan(Rng As Range, Limit As Double) As Variant()

Dim inputCell As Range ' each cell we read from
Dim resultCount As Integer ' number of matching cells found
Dim resultValue() As Variant ' array of cell values

resultCount = 0
ReDim resultValue(1 To 1, 1 To Rng.Cells.Count)

For Each inputCell In Rng
    If Abs(inputCell.Value) >= Limit Then
        resultCount = resultCount + 1
        resultValue(1, resultCount) = inputCell.Value
    End If
Next inputCell

' Output array must be two-dimensional and we can only
' ReDim Preserve the last dimension
If (resultCount > 0) Then
    ReDim Preserve resultValue(1 To 1, 1 To resultCount)
Else
    resultValue(1, 1) = CVErr(xlErrNA)
    ReDim Preserve resultValue(1 To 1, 1 To 1)
End If

' Transpose the results to produce a column rather than a row
resultValue = Application.WorksheetFunction.Transpose(resultValue)

FilterGreaterThan = resultValue

End Function

edit: works OK for me with the test values in the comment below:

Excel file showing FilterGreaterThan UDF working correctly

I'm sure you know this but don't include the { or } characters when entering the array formula - Excel adds them in after you've hit Ctrl-Shift-Enter

Thank you! Unfortunately, I continue to get a #value error with your code. Any thoughts? Example I am using: Rng values to filter: -0.003742158,0.004134996,-0.002937923 Limit: 0.0008 — user1666842, Sep 12 '12 at 23:37
Yours is the right answer. The only change I'd make is Dim the array to full size at start and `ReDim Preserve resultValue(1 To 1, 1 To resultCount)` once only at the end before transpose - you've got the max array size with `rng.cells.count`. Also, both of ours treat resultValue as a value rather than array if only 1 cell :) — , Sep 12 '12 at 23:57
@ooo thanks - I've updated my answer to incorporate these suggestions — barrowc, Sep 13 '12 at 00:36

Excel UDF Filter Range

2 Answers2

Linked