title: Excel UDF to find first and last cell in range with a given value - runs slowly
body: <p>I'm writing a function which takes a column range and finds the first and last cell in that column which have a certain value. This gives a first row number and a last row number that are then used to return the corresponding subrange in another column.<br>
  The idea is that with this function I can apply Excel functions to a (continuous) subsection of a range. E.g. suppose I have a table with various prices of Apples and Bananas, grouped so that all prices of Apples come first, then Bananas. I want to find the minimum price of Apples and the minimum of Bananas, but selecting the whole range and without changing the range over which to minimise. I would use my desired function to feed a range to Excel's MIN function which included just Apples, or just Bananas, without having to manually select these subranges. A MINIF, if you will - like a weak version of SUMIF but for MIN (and potentially many other functions).<br>
  I've found a way of doing it but it's running really quite slow. I think it may have to do with the for loop, but I don't understand enough about efficiency in Excel/VBA to know how to improve it. I'm using this code on an Excel table, so the columns I pass are named columns of a table object. I'm using Excel 2010 on Windows 7 Enterprise.</p>
  
  <p>Grateful for any help. Even solutions on how to conditionally apply functions to ranges that deviate radically from this are well received.</p>
  
  <p>Code:</p>
  
  <pre><code>' ParentRange and CriterionRange are columns of the same table. 
  'I want to extract a reference to the part of ParentRange which corresponds
  'by rows to the part of CriterionRange that contains cells with a certain value.
  Function CorrespondingSubrange(CriterionRange As Range, Criterion As _
  String, ParentRange As Range) As Range
  
  Application.ScreenUpdating = False
  
  Dim RowCounter As Integer
  Dim SubRangeFirstRow As Integer
  Dim SubRangeFirstCell As Range
  Dim SubRangeLastRow As Integer
  Dim SubRangeLastCell As Range
  Dim RangeCountStarted As Boolean
  
  RangeCountStarted = False
  
  Set SubRangeFirstCell = CriterionRange.Find(What:=Criterion, LookIn:=xlValues, _
          LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, _
          MatchCase:=False, SearchFormat:=False)
  
  If Not (SubRangeFirstCell Is Nothing) Then
      RangeCountStarted = True
      SubRangeFirstRow = SubRangeFirstCell.Row - CriterionRange.Range("A1").Row + 1
  
      For RowCounter = SubRangeFirstRow To CriterionRange.Cells.Count
  
          If Not (CriterionRange.Cells(RowCounter, 1).Value = Criterion) Then
              SubRangeLastRow = RowCounter - 1
              Exit For
          End If
  
      Next
  
  End If
  
  If RangeCountStarted = True And SubRangeLastRow = 0 Then SubRangeLastRow = RowCounter
  
  Set CorrespondingSubrange = ParentRange.Range("A" &amp; SubRangeFirstRow &amp; ":A" &amp; SubRangeLastRow)
  
  Application.ScreenUpdating = True
  End Function
  </code></pre>