为什么"Range(...).End(xlDown).Row"返回1048576吗? [英] Why does "Range(...).End(xlDown).Row" return 1048576?

问题描述

我想在VBA的for循环中复制一些值.因此，我通过以下方式计算限制:

I want to copy some values in a for-loop in VBA. Therefore I calculate the limits by:

For iCounter = 1 To (Range(...).End(xlDown).Row - Range(...).Row  + 1)

严重地Range(...).End(xlDown).Row返回1048576.当我调试时，值突然变为正确的值.相同的代码在VBA代码中的其他一些地方也能很好地工作.

Sadly Range(...).End(xlDown).Row returns 1048576. When I debug, suddenly the value changes to the right one. The same Code works well in some other locations in the VBA Code.

推荐答案

要记住的主要事情是End方法在VBA中再现了使用Ctrl +箭头键的功能.这些旨在在值块之间导航.

The main thing to keep in mind is that the End method reproduces in VBA the functionality of using Ctrl+Arrow Keys. These are meant to navigate between blocks of values.

从A1开始:

点击Ctrl+Down:

这说明了在包含多个单元格的块的开头或结尾处使用Ctrl+Down时发生的情况–转到该块的结尾.当您说有时代码运行良好时，就是在隐式指代的情况.

This illustrates what happens when you use Ctrl+Down at the beginning or end of a block consisting of multiple cells -- you go to the end of that block. This is the case that you are implicitly referring to when you said that sometimes the code works well.

现在-再次点击Ctrl+Down:

您跳到了下一个方块.现在，再做一次:

You jumped to the next block. Now, do it again:

该块的底部.最后，再次:

The bottom of that block. Finally, again:

嗯-没有下一个要去的地方-所以它一直向下走.这对应于使您感到奇怪的情况.它把您放在这里:

Well -- there is no next block to go to -- so it goes all the way down. This corresponds to the case that strikes you as weird. It puts you here:

但是-现在发生了一些很酷的事情:按下Ctrl + Up-Excel将在 up 中搜索下一个块:

But -- something cool happens now: Press Ctrl + Up -- and Excel searches up for the next block:

这是列A中有数据的最后一个单元格.

And this is the last cell with data in column A.

由于这个原因-您在Excel VBA中经常看到以下代码:

For this reason -- you see the following code a lot in Excel VBA:

Cells(Rows.Count,1).End(xlUp)

要获取一列中最后使用的单元格(在这种情况下为1)，或者如果整个列为空白，则获取该列中的第一个单元格.

To get the last used cell in a column (1 in this case) or the first cell in the column if the overall column is blank.

这篇关于为什么"Range(...).End(xlDown).Row"返回1048576吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！