vba Ctrl-End 不会将我带到最后一个单元格
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/16435582/
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
Ctrl-End doesn't bring me to the last cell
提问by Question Everything
Ctrl-end key is supposed to bring me to the bottom, right-most cell. However, on several cases, it goes beyond that, meaning it goes hundreds of cells down, even if there are actually no data on those cells.
Ctrl-end 键应该将我带到底部，最右边的单元格。然而，在某些情况下，它超出了这个范围，这意味着它会降低数百个单元格，即使这些单元格实际上没有数据。
Can you advise a VBA script on how to clean-up those possibly invisible data that causes this? I'm pretty sure it doesn't work with a regular delete as despite doing that, it still behaves the same way.
您能否就如何清理导致这种情况的那些可能不可见的数据向 VBA 脚本提供建议？我很确定它不适用于常规删除，尽管这样做，它的行为方式仍然相同。
回答by Mike Woodhouse
Ctrl-End does take you to the bottom-right cell, it just uses different rules to what you'd expect in deciding what that cell should be. Just about any cell that's been referenced in any way will be included in the decision. It's annoying, but that's how Excel "works".
Ctrl-End 确实会将您带到右下角的单元格，它只是使用与您在决定该单元格应该是什么时所期望的不同的规则。几乎以任何方式引用的任何单元格都将包含在决策中。这很烦人，但这就是 Excel“工作”的方式。
Here's a description of how to address the problem.
To paraphrase:
转述：
- Select the last cell that contains data in the worksheet
To delete any unused rows:
- Move down one row from the last cell with data.
- Hold the Ctrl and Shift keys, and press the Down Arrow key
- Right-click in the selected cells, and, from the shortcut menu, choose Delete
- Select Entire Row, click OK.
delete entire row
To delete any unused columns:
- Move right one column from the last cell with data.
- Hold the Ctrl and Shift keys, and press the Right Arrow key
- Right-click in the selected cells, and, from the shortcut menu, choose Delete
- Select Entire Column, click OK.
Save the file. Note: In older versions of Excel, you may have to Save, then close and re-open the file before the used range is reset.
- 选择工作表中包含数据的最后一个单元格
要删除任何未使用的行：
- 从带有数据的最后一个单元格向下移动一行。
- 按住 Ctrl 和 Shift 键，然后按向下箭头键
- 右键单击选定的单元格，然后从快捷菜单中选择删除
- 选择整行，单击确定。
删除整行
要删除任何未使用的列：
- 从带有数据的最后一个单元格向右移动一列。
- 按住 Ctrl 和 Shift 键，然后按右箭头键
- 右键单击选定的单元格，然后从快捷菜单中选择删除
- 选择整列，单击确定。
保存文件。注意：在旧版本的 Excel 中，您可能需要先保存，然后关闭并重新打开文件，然后才能重置使用的范围。
回答by Robert Mearns
This script will reset the last used row for any sheet that is active when it is run.
此脚本将重置任何在运行时处于活动状态的工作表的最后使用的行。
Sub Reset()
Dim lngCount As Long
lngCount = ActiveSheet.UsedRange.Rows.Count
End Sub
