Excel Developer Tip

Excel page

Tip archives

Looping Through Ranges Efficiently in Custom Worksheet Functions

While browsing through the Excel Forum on CompuServe, I discovered the solution to a problem that has been bothering me since Excel 5 was first released. Consider the following custom worksheet function.

Function CountBetween(InRange, Lower, Upper)
    TheCount = 0
    For Each Cell In InRange
        If Cell.Value >= Lower And Cell.Value <= Upper _
          Then TheCount = TheCount + 1
    Next Cell
    CountBetween = TheCount
End Function

This function returns the number of cells in a range that fall between two values. The first argument is a range, the second argument is the lower comparison value, and the third argument is the upper comparison value. If you wanted to count the number of values between 1 and 5 in the range A1:A20, you could use this formula:

  =CountBetween(A1:A20,1,5)

This function works fine in most situations. However, try entering the following formula and see what happens:

  =CountBetween(A:A,1,5)

You'll find that evaluating this function seems to take forever since it will loop through all cells in the range -- even those that are beyond the worksheet's "used range."

My original approach to solving this problem was to use the SpecialCells method to create a subset of the input range that consisted only of nonempty cells. However, I discovered that SpecialCells is off-limits inside of a worksheet function. So I pretty much wrote this off as a problem with Excel.

But, thanks to the folks who frequent the Excel Forum on CompuServe, I now know of a solution. The function below uses the Intersect function to create a new range object that consists of the intersection of the UsedRange and the input range.

Function CountBetween2(InRange, Lower, Upper)
    Set SubSetRange = _
        Intersect(InRange.Parent.UsedRange, InRange)
    TheCount = 0
    For Each Cell In SubSetRange
        If Cell.Value >= Lower And Cell.Value <= Upper Then _
            TheCount = TheCount + 1
    Next Cell
    CountBetween2 = TheCount
End Function

The addition of the Set statement solves the problem. You'll find that this function works equally fast with either of these formulas:

  =CountBetween(A1:A20,1,5)
  =CountBetween(A:A,1,5)

This technique can be adapted to any custom worksheet function that accepts a range argument and loops through each cell in the range.

Thanks to the following who contributed to this informative thread on the CompuServe forum: Bob Flanagan, John V. Green, Myrna Larson, Bill Manville, and Dave Martin.