Coding a loop to pick intermediate data points within a Column

[WayneSW]

Hello, how can I loop through a column and pick intermediate data points within that column, meaning say a column containing 40 data points. how can i code a loop through in vba to pick only the lowest data points, be it data points 5, 12, 21, 24, 28, 30, 33, 38. how can i code this part within the looping to place the data points within a array, to only pick the lowest data points within the column of 40 data points. Any help would be appreciated, I tried using min(), but not quite right. Thanks in advance.

 

[footoo]

Assuming the data is in column A and you want the lowest 8 numbers :

Sub v()

Dim col As Range: Set col = [A:A] 'change as required
Dim lowest&: lowest = 8 'change as required
Dim x&
ReDim ray(1 To lowest) As Variant

For x = 1 To lowest
    ray(x) = WorksheetFunction.Small(col, x)
Next

For x = LBound(ray) To UBound(ray)
    Debug.Print ray(x)
Next x

End Sub

 

[WayneSW]

Assuming the data is in column A and you want the lowest 8 numbers :

Sub v()

Dim col As Range: Set col = [A:A] 'change as required
Dim lowest&: lowest = 8 'change as required
Dim x&
ReDim ray(1 To lowest) As Variant

For x = 1 To lowest
    ray(x) = WorksheetFunction.Small(col, x)
Next

For x = LBound(ray) To UBound(ray)
    Debug.Print ray(x)
Next x

End Sub

thanks for the code, it picks the low, but it picks them right back to back, I would like to have it scan the column and pick say the lowest 8 data points within the column or whatever number i choose, intermediate, i understand that there could be 3 or more low data points right together. I guess using an average would make it work better with choosing the lowest data points. Any ideas? Thanks

 

[footoo]

Can you post some some sample data to illustrate the problem?

 

[footoo]

Try :

Sub v()
Dim lowest&: lowest = 8 'change as required
Dim r&: r = Cells(Rows.Count, "A").End(xlUp).Row 'change as required
Dim rng As Range: Set rng = Range("A1:A" & r) 'change as required

Dim d As Object: Set d = CreateObject("scripting.dictionary")
Dim c As Range, ray, x

For Each c In rng
    If Len(c.Value) > 0 Then d(c.Value) = d(c.Value)
Next

ReDim ray(1 To d.Count)
ray = d.keys

For x = 1 To lowest
    Debug.Print Application.Small(ray, x)
Next
End Sub

 

[WayneSW]

Thanks so much for the code, it looks like this is working, I will do some testing to check more, thanks again