最后一个单元格选择 .End(xlup) 在 Excel 2003“列表”中使用 VBA - 需要两个 .Select 才能获得正确的单元格？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3505155/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Last cell selection .End(xlup) using VBA in Excel 2003 'lists' - requires two .Select to get correct cell?
提问by goofology
I'm a new convert from Experts-Exchange since I noticed they increased the 'Free Premium Services' points threshold.
我是 Experts-Exchange 的新转换者，因为我注意到他们提高了“免费高级服务”积分门槛。
It seems that Excel 2003 has issues with the End(xlup) command when using it in a worksheet that contains an Excel 'List'.. If I select a cell outside the 'list' boundary, and then try to select the last cell in the worksheet by using VBA, I have to call the .Select function twice to make sure I am getting the correct cell. If the original cell is inside the 'list' boundarythen i only need one .Select. My hacked together solution is below, with two selects, as I can never be sure what cell may be selected on save. I include a version check at open to run different code in Excel 2007 (this code fails in 2007, where the .End(xlUp) command works properly).
似乎 Excel 2003 在包含 Excel“列表”的工作表中使用 End(xlup) 命令时存在问题。如果我选择“列表”边界之外的单元格，然后尝试选择最后一个单元格通过使用 VBA 工作表，我必须调用 .Select 函数两次以确保我得到正确的单元格。如果原始单元格在“列表”边界内，那么我只需要一个 .Select。我的组合解决方案如下，有两个选择，因为我永远无法确定保存时可以选择哪个单元格。我在打开时包含一个版本检查以在 Excel 2007 中运行不同的代码（此代码在 2007 年失败，其中 .End(xlUp) 命令正常工作）。
Is there a more eloquent way to handle this? Thanks for any help!
有没有更雄辩的方法来处理这个问题？谢谢你的帮助！
.Range("A1").Select
.Cells(.Rows.Count, "A").End(xlUp).Select
.Cells(.Rows.Count, "A").End(xlUp).Select
'two .Selects needed to select correct cell in Excel 2003 list because original selection (A1) was not in list'
.Range("A1").Select
.Cells(.Rows.Count, "T").End(xlUp)(-2, 1).Select
.Cells(.Rows.Count, "T").End(xlUp)(-2, 1).Select
'two .Selects needed to select correct cell in Excel 2003 list because original selection (A1) was not in list'
.Cells(.Rows.Count, "T").End(xlUp)(-3, 1).Select
'only one select needed here because original selection above was within the list'
回答by Oorang
See how this does:
看看这是怎么做的：
Sub Example()
Dim rngLstCell As Excel.Range
Set rngLstCell = GetLastCell(Excel.Worksheets("Sheet1"))
MsgBox "The last cell is: " & rngLstCell.Address, vbInformation
End Sub
Public Function GetLastCell(ByVal ws As Excel.Worksheet) As Excel.Range
Dim rngRtnVal As Excel.Range
With ws.UsedRange
Set rngRtnVal = .Find("*", .Cells(1, 1), , , xlByRows, xlPrevious)
If rngRtnVal Is Nothing Then
Set rngRtnVal = .Cells(1, 1)
End If
End With
Set GetLastCell = rngRtnVal
End Function
Using find may seem weird at first but it ends up being the most reliable way due to some vagaries in the way empty cells are handled.
使用 find 起初可能看起来很奇怪，但由于处理空单元格的方式有些变幻莫测，它最终成为最可靠的方法。
This may not be perfect if your data is non-normalized (jagged).
如果您的数据未标准化（锯齿状），这可能并不完美。
回答by goofology
I found that my use of .End(xlUp).Select prior to acting on the .End(xlUp) cell was causing the problem. If I simply avoid the .End(xlUp).Select prior to operating on the .End(xlUp) cell, the problem is less complex. It can be easily solved by preceding any .End(xlUp) operation with .Range("A1").Select. See the code for explanation. This doesn't really fix the problem with improper .End(xlUp) cell 'selection' - but I'm not interested in 'selecting' the cells, just operating on them. I must mention that I use .Range("A1").Select because A1 is outside of the 'list' that I'm manipulating via VBA.
我发现我在对 .End(xlUp) 单元格执行操作之前使用 .End(xlUp).Select 导致了问题。如果我只是在对 .End(xlUp) 单元格进行操作之前避免使用 .End(xlUp).Select，那么问题就不那么复杂了。它可以通过在任何 .End(xlUp) 操作之前使用 .Range("A1").Select 轻松解决。请参阅代码以获取解释。这并不能真正解决不正确的 .End(xlUp) 单元格“选择”的问题 - 但我对“选择”单元格不感兴趣，只是对它们进行操作。我必须提到我使用 .Range("A1").Select 因为 A1 在我通过 VBA 操作的“列表”之外。
'commented out - just need to add a ".Range("A1").Select" prior to any .End(xlUp) usage (besides .End(xlUp).Select) to make it work in Excel 03
'.Range("A1").Select
'.Cells(.Rows.Count, "A").End(xlUp)(0, 1).Select
'.Cells(.Rows.Count, "A").End(xlUp)(0, 1).Select
''two .Selects needed to select correct cell in Excel 2003 'Lists'
'Set EntryDate = Cells(.Rows.Count, "A").End(xlUp)(0, 1) 'no need to select cell first, then operate on it, as in the code above
'fixed code below
.Range("A1").Select 'needed for Excel 03 to select correct cell
Set EntryDate = Cells(.Rows.Count, "A").End(xlUp) 'just operate on the cell instead of selecting it first
回答by Nick Spreitzer
Are you sure the ranges that you were working with were identical? You shouldn't get different results using the End property in Excel 2007 versus 2003.
您确定您使用的范围相同吗？使用 Excel 2007 和 2003 中的 End 属性不应得到不同的结果。
Looking at your code:
查看您的代码：
.Range("A1").Select
.Cells(.Rows.Count, "A").End(xlUp).Select
.Cells(.Rows.Count, "A").End(xlUp).Select
Each of these lines of code have exactly zero impact on one another. No honest explanation can be offered about why the End property is giving you different results based on the code you've provided. From what's written, you should get the same results each time. (That's assuming you're working with identical ranges.) I'd be suspicious of any other code that's being executed. I can offer a couple general tips though: If you use End starting with a blank cell, it will stop at the first non-blank cell. If you start with a non-blank cell, the opposite is true. Looking at the screenshot below:
每一行代码对彼此的影响都为零。对于 End 属性为什么会根据您提供的代码为您提供不同的结果，无法提供诚实的解释。从所写的内容来看，您每次都应该得到相同的结果。（假设您正在使用相同的范围。）我会怀疑正在执行的任何其他代码。不过，我可以提供一些一般提示：如果您使用 End 以空白单元格开头，它将在第一个非空白单元格处停止。如果从非空白单元格开始，则相反。看看下面的截图：
Range("B13").End(xlUp).Select 'selects B12
Range("B12").End(xlUp).Select 'selects B2
Range("A12").End(xlUp).Select 'selects A6
So whether or not your list is contiguous is an issue. Also, it is not necessary to select a range before you do something to it. Telling Excel to select cell A1 does not impact how it executes .Cells(.Rows.Count, "A").End(xlUp).Select. Assuming that line is within a With block that refers to a worksheet, that line of code is the same thing as navigating to cell A65536 (or A1048576 in Excel 2007) and pressing Ctrl + Up. Assuming that cell is blank, Excel will navigate upwards until it finds the first non-blank cell in column A. If your With block refers to a range object, then that line of code will go to the first column, bottom row of that range and navigate upwards until it comes to the first blank or non-blank cell.
因此，您的列表是否连续是一个问题。此外，在对它做一些事情之前没有必要选择一个范围。告诉 Excel 选择单元格 A1 不会影响它的执行方式.Cells(.Rows.Count, "A").End(xlUp).Select。假设该行位于引用工作表的 With 块内，该行代码与导航到单元格 A65536（或 Excel 2007 中的 A1048576）并按 Ctrl + Up 相同。假设单元格为空白，Excel 将向上导航，直到在 A 列中找到第一个非空白单元格。如果您的 With 块引用一个范围对象，那么该行代码将转到该范围的第一列，底行并向上导航，直到到达第一个空白或非空白单元格。
