For 循环中的设置范围

发布于 2024-12-01 14:15:36 字数 313 浏览 0 评论 0原文

我正在尝试在 For 循环中设置范围。当我这样做时，我的代码工作正常：

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

但是当我这样做时，我没有得到相同的结果：

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

这两个代码不是等价的吗？我应该对第二个进行哪些更改，使其与第一个的执行方式相同，但不会使我在代码中对范围进行硬编码？

原文

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?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

予囚 2024-12-08 14:15:36

您拥有的第二个单元格仅获取范围内的最后一个单元格，我相信这将是第一个示例中的 A5 。相反，你需要做这样的事情。

我将其构建为一个小测试，以便您可以看到第一个选项、更正后的第二个选项，以及我希望如何执行此操作的示例。

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

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

回复收藏 0 原文

两仪 2024-12-08 14:15:36

最简洁的方法可能是将lastRow 数字存储在变量中，如下所示。您可以在每一行中进行串联：

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

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

请注意，使用 xlUp 和 xlDown 之间存在差异。

xlUp 为您提供 A 列中使用的最后一个单元格（因此您从 rows.count 开始）
XlDown 为您提供最后一个非空白单元格（您可以使用 range("A1").End(xlDown).Row ）

您会注意到很多人使用“A65536”而不是 rows.count，但 65536 并不是某些版本的 Excel 的限制，因此使用 rows.count 总是更好。

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.

回复收藏 0 原文

~没有更多了~