How to find smallest values in AutoFiltered range?

Question

I have a column with two value ranges, specifically 0-30000 and 60000+ from which I need to extract the smallest two values in the 60000 range.

The only way I have found so far to to use AutoFilter to produce the subset of the required data to extract. My problem is that the Autofilter function does not return a reference to a range. If it did I could use the SMALLfunction to obtain the values I am looking for.

How can I do the filtering and extraction of the two smallest values from this filtered data?

Answer 1

I abandoned the idea of using Autofilter. Instead, I went with a combination of SMALL and a loop.

Cells(2, secIdCol).Select
Set valsRange = Range(Selection, Selection.End(xlDown))

For Each val In valsRange
    If val.Value < 599999 Then
        val.Value = "" // I don't save changes 
        val1 = Application.WorksheetFunction.Small(valsRange, 1)
        val2 = Application.WorksheetFunction.Small(valsRange, 2)
    End If
Next val

Answer 2

I do not think you necessarily need VBA. Have you considered an Array formula, entered using ctrl+shift:

   =MIN(IF(A1:A7>2999,A1:A7))

Answer 3

Here's a different approach without using the SMALL worksheet function:

With Worksheets("Sheet1")
    Dim lastRow As Long
    lastRow = .Cells(2, secIdCol).CurrentRegion.Rows.Count + 1

    Dim rowIndex As Long
    Dim currentValue As Long
    Dim val1 As Long
    Dim val2 As Long

    ' Set val1 to maximum possible long value
    val1 = 2147483647
    For rowIndex = 2 To lastRow
        currentValue = CLng(.Cells(rowIndex, secIdCol).Value)
        If (currentValue > 59999) Then
            If (currentValue < val1) Then
                val2 = val1
                val1 = currentValue
            End If
        End If
    Next rowIndex
End With

MsgBox val1 & " | " & val2