Setting Range in For Loop

Question

I am trying to set the range in For loop. My code works fine when I do this:

For Each i in Range("A1":"A5")
   'Some process code
Next i

But I do not get the same results when I do this:

For Each i in Range("A1").End(xlDown)
    'Some Process
Next i

Arent the two codes equivalent? What changes should I make to the second one that it perfoms the same way as the first one but doesn't make me hardcode the Range in the code?

Solution 1

The second one you have only gets the last cell in the range, which I believe would me A5 from the first example. Instead, you need to do something like this.

I structured this like a small test so you can see the first option, the corrected second, and an example of how I would prefer to do this.

Option Explicit

Sub test()

  Dim r As Range
  Dim x As Range

  ' Make sure there is stuff in Range("A1:A5")
  Range("A1") = 1
  Range("A2") = 2
  Range("A3") = 3
  Range("A4") = 4
  Range("A5") = 5

  ' Your first option
  For Each x In Range("A1:A5")
    Debug.Print x.Address & ", " & x
  Next

  ' What you need to do to get the full range
  For Each x In Range("A1", Range("A1").End(xlDown))
    Debug.Print x.Address & ", " & x
  Next

  ' My preferred method
  Set r = Range("A1").End(xlDown)
  For Each x In Range("A1", r)
    Debug.Print x.Address & ", " & x
  Next

End Sub

Solution 2

The cleanest way to do it would probobly be to store the lastRow number in a variable like so. You can do the concatenation in the for each line:

Dim cell as range
Dim lastRow As Long
lastRow = Range("A" & Rows.Count).End(xlUp).row

For Each cell In Range("A1:A" & lastRow)

Please note that it makes a difference between using xlUp and xlDown.

• xlUp gives you last cell used in column A (so you start at rows.count)
• XlDown gives you last non-blank cell (you can use range("A1").End(xlDown).Row)

You'll notice a lot of people use "A65536" instead of rows.count, but 65536 is not the limit for some versions of Excel, so it's always better to use rows.count.

Comments

[Ali]

I think the third method is the best, its clean and allows for better code reading. Thanks.