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.

brettdj · Accepted Answer · 2015-02-25 03:58:58Z

3

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

answered Feb 25, 2015 at 3:58

brettdj

55.9k16 gold badges120 silver badges178 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Jeanno · Accepted Answer · 2015-02-24 22:24:58Z

1

Use a For loop, Rows.Count property, Columns.Count

Dim i as long
For i = 1 to Selection.Rows.Count
   For j = 1 To Selection.Columns.Count
       Cells(i, j).Value ' Use this to access the value of cell in row i and column j

edited Feb 24, 2015 at 22:24

answered Feb 24, 2015 at 21:14

Jeanno

2,8894 gold badges26 silver badges33 bronze badges

1 Comment

hotkoreanchick Over a year ago

Okay, that goes through my selected range but that doesn't necessarily activate each row for me to access the info I need from each row??

Collectives™ on Stack Overflow

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

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related