vba 强制刷新工作表的“最后一个”单元格
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2107047/
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
Force Refresh of "Last" Cell of the Worksheet
提问by Laurent
Pressing Ctrl+Endin Excel takes you to the bottom-right-most cell of the worksheet.
在 Excel 中按Ctrl+End会将您带到工作表最右下角的单元格。
If you delete the last rows or columns and save the workbook, this last cell gets updated, as well as the scrollbars.
如果您删除最后的行或列并保存工作簿，则最后一个单元格以及滚动条都会更新。
I remember there was a one line VBA command that you could run that would do the update without having to save the workbook, but I can't remember what the command is - do you have any ideas?
我记得有一个单行 VBA 命令可以运行，它可以在无需保存工作簿的情况下进行更新，但我不记得命令是什么 - 你有什么想法吗？
回答by Laurent
Here is the answer:
这是答案：
Sub x()
ActiveSheet.UsedRange
End Sub
Run this and the last cell will be reset.
运行这个，最后一个单元格将被重置。
回答by user3166377
I've found something that consistently works to delete those blank rows. You can tell when the “used range” excel is using is too big and is going to add extra blank rows when you use the scroll bar to the right and it goes beyond the last row of actual data when you scroll to the bottom. This will caused extra blank records to be added to the table when it is imported into SQL.
我发现了一些始终可以删除这些空白行的东西。当您使用右侧的滚动条时，您可以判断“使用的范围”excel 何时使用过大并且会添加额外的空白行，而当您滚动到底部时，它会超出实际数据的最后一行。这将导致在将表导入 SQL 时将额外的空白记录添加到表中。
To get rid of them:
要摆脱它们：
- Select the entire first row under the last row of data. Hit Ctrl+ Shift+ Down Arrowto select all the blank rows.
- From the Home Tab Select Clear and then Clear All from the Edit menu (picture of a whitish-grey eraser).
- Right-click and select Delete.
- Goto cell A1 first and then Save.
- Press Ctrl+ Homeand then Ctrl+ End(This should take you the correct last cell in the used range (above blank rows).
- Click Save again.
- 选择最后一行数据下的整个第一行。点击Ctrl+ Shift+Down Arrow选择所有空白行。
- 从“主页”选项卡中选择“清除”，然后从“编辑”菜单中选择“全部清除”（灰白色橡皮擦图片）。
- 右键单击并选择删除。
- 首先转到单元格A1，然后保存。
- 按Ctrl+ Home，然后Ctrl+ End（这应该带您在使用范围内正确的最后一个单元格（以上空行）。
- 再次单击保存。
回答by UJJWAL DAS
When none of the above works try this.
当以上都不起作用时，试试这个。
Select the unused rows and change the row height.
选择未使用的行并更改行高。
Now delete the rows and save.
现在删除行并保存。
Bingo!
答对了！
回答by Reverus
Here's what I did... since noneof the above worked (this time that is, which is sad cause this code was running beautifully then all the sudden xlCellTypeLastCelltotally failed me.) This will only work if you hardcode the first cell of the region you wanna grab the last cell of... for example I was pasting data tables into a sheet of 12 - 40 columns and 60-90 rows... but since it was a paste, it always started in cell A79...
这就是我所做的......因为以上都没有奏效（这一次，这是可悲的，因为这段代码运行得很好，然后突然间xlCellTypeLastCell我完全失败了。）这只有在你对该区域的第一个单元格进行硬编码时才有效您想获取...的最后一个单元格，例如，我将数据表粘贴到 12 - 40 列和 60-90 行的工作表中...但由于它是粘贴，所以它总是从单元格 A79 开始...
Worksheets("Data_Import").Activate
Range("A79").CurrentRegion.Select
A = Selection.Rows.Count - 1
B = Selection.Columns.Count - 1
Selection.Offset(A, B).Resize(1, 1).Select
Set DataEnd = Selection
I feel sad to NOT use the cool special cells thing, but alas, if it doesn't work! then I just can't use it. :C
不使用很酷的特殊细胞，我感到很难过，但唉，如果它不起作用！那我就不能用了。：C
p.s. - you could also throw in a
ps - 你也可以扔一个
ActiveSheet.Cells(Rows.Count, 1).End(xlUp).CurrentRegion.Select
回答by irockyoursocks
For some reason the code ActiveSheet.UsedRangealone did not work for me on Excel 2016 64-bit to force Excel to reset the last used cell.
出于某种原因，ActiveSheet.UsedRange单独的代码在 Excel 2016 64 位上对我不起作用以强制 Excel 重置上次使用的单元格。
This does work for me. Hope this helps.
这对我有用。希望这可以帮助。
Rows(25:415).Clear 'deletes contents and formatting
ActiveSheet.UsedRange 'resets last used cell
回答by Noah Smith
This solution works for Excel 2013, but may also work for most recent versions of Excel:
此解决方案适用于 Excel 2013，但也适用于最新版本的 Excel：
- Choose the worksheet where you want to change the last cell, and delete any unused rows and columns
- Click on File - Options - Customize Ribbon
- Under Main Tabs, check the box next to "Developer", then click OK
- On the Developer ribbon that now appears, click Visual Basic
- In the upper-left corner, under Microsoft Excel Objects, click on the Sheet Name where you want to force a refresh of the worksheet's last cell
- In the menu, click on Run - Refresh, then close the Visual Basic Window
- 选择要更改最后一个单元格的工作表，并删除所有未使用的行和列
- 点击文件-选项-自定义功能区
- 在主选项卡下，选中“开发人员”旁边的框，然后单击确定
- 在现在出现的开发人员功能区上，单击 Visual Basic
- 在左上角的 Microsoft Excel 对象下，单击要强制刷新工作表最后一个单元格的工作表名称
- 在菜单中，单击运行 - 刷新，然后关闭 Visual Basic 窗口
When you hit Ctrl + End, your last cell should now be refreshed.
当您按 Ctrl + End 时，现在应该刷新您的最后一个单元格。
回答by Bjyn
Check out http://dmcritchie.mvps.org/excel/lastcell.htm#MakeLastCell, found the link from a similar question.
查看http://dmcritchie.mvps.org/excel/lastcell.htm#MakeLastCell，找到了来自类似问题的链接。
Far from the forgotten one liner, but did solved the problem for me.
远离被遗忘的一个班轮，但确实为我解决了问题。
Alternatively;
或者;
Turn on manual calculation (to preserve references).
create new sheet.
Copy cells and Name of old sheet.
打开手动计算（以保留参考）。
创建新工作表。
复制单元格和旧工作表的名称。
回答by Brian
I'm not sure why everyone is making it so complicated. Just press Ctrl + PgDn.
我不知道为什么每个人都把它弄得这么复杂。只需按 Ctrl + PgDn。
