How to go through each row within a selected range using VBA

Question

Ideally, I would have a range selected and then I would run the macro and I want the macro to essentially run a loop to go through each row so I can extract information from each row until it reaches the end of the range.

For example, A6:B9 are selected, first I want to focus on A6:B6. As in I want to be able to find the min value of the two cells for instance, using my MinSelected function(stated below) which requires a selected range which would ideally be A6:B6. And I want to do this for each row until the end of the original range.

Function MinSelected(R As Range)
    MinSelected = Application.WorksheetFunction.min(R)
End Function

Is there any way to do this??? Please tell me to clarify anything that's unclear. Thanks in advance.

Answer 1

You can loop through rows - but looping through a variant array is more efficient (for many rows)

variant aray

Dim X
Dim lngCnt As Long
X = Range("A6:B9").Value2
For lngCnt = 1 To UBound(X)
    Debug.Print Application.Min(Application.Index(X, lngCnt))
Next

range approach

Dim rng1 As Range
Dim rng2 As Range
Set rng1 = Range("A6:B9")
For Each rng2 In rng1.Rows
    Debug.Print Application.Min(rng2)
Next