Is there a way to tell when one cell is below another?

Question

I'm using excel vba, and I'm trying to do a loop until the ranges I'm dealing with go beyond some other pre-defined range.

For example, I'd set one range to range("A10000"), and I'd like to loop on the a column until something passed that point.

Psudo-code of what I'd like is below.

dim myRange as range
dim maxRange as range

do while myRange < maxRange
  ' do something
  ' range.address = range.address next one '(range("a450") becomes range("a451"))
end while

EDIT: The reason I'm looking explicitly for a comparison to terminate the loop is because I can't assume a well-defined space that I can do a straight-forward loop over each cell.

E. A. Bagby · Accepted Answer · 2015-01-05T22:28:35.417

Is this what you are trying to do?

Public Sub TEST()


    Dim myRange As Range
    Dim maxRange As Range
    Dim i as Integer
    Set myRange = Sheets(1).Range("A2")
    Set maxRange = Sheets(1).Range("A5")

    Do While myRange.Row < maxRange.Row
      ' do something
      ' range.address = range.address next one '(range("a450") becomes range("a451"))
      i = myRange.Row + 1
      Set myRange = Sheets(1).Range("A" & i)
    Loop



End Sub

score 2 · Answer 2 · edited May 23 '17 at 12:05

2

Though Edward's answer seems like a perfectly reasonable way to tackle this challenge (Range.row ought to get you where you're going), when I need to operate on Range objects and have a really well-defined space I reach for the For Each Cell in Range construct.

Here's a tiny example:

Dim rngTarget As Range, rngCell As Range

Set rngTarget = Sheets(1).Range("A2:A10000")

For Each rngCell In rngTarget
    'do cool stuff to rngCell
Next rngCell

And there you have it! I particularly like this method because it is concise, handles movement in the x and y-direction without juggling columns/rows and reads easily.

edited May 23 '17 at 12:05

Community

1
1

answered Jan 06 '15 at 02:49

Dan Wagner

2,693
2
13
18

I'd go with this one if I were to do the same. – L42 Jan 06 '15 at 03:02
Agreed this a good method, but if for some reason he is looking to compare range values, than my method might be the way to go. Just answering the question as presented. – E. A. Bagby Jan 06 '15 at 03:20
@EdwardBagby, The reason I was looking to compare range values is because I don't expect to always increment by a value of one. The problem is that I don't have a well-defined space to work with, so there's going to be a lot of "hopping around", and I needed a termination clause. Thanks so much for your help, btw. (I can edit the original question to make the context more clear.) – user38858 Jan 06 '15 at 18:12
Yes, you are welcome. Actually I had thought to comment to that I usually prefer explicit loops over ones based on less code, if practical. The intention is obvious and there's more control for unusual circumstances. For example, a two-dimensional array of cells using Dan's code may not cycle through those dimensions the way you prefer. – E. A. Bagby Jan 07 '15 at 03:18

Is there a way to tell when one cell is below another?

2 Answers2