Excel重置“UsedRange” [英] Excel resetting "UsedRange"

问题描述

不知道我失踪了，但我看到的示例看起来似乎不起作用。

我从网络查询导入数据。我将查询设置为在重新查询时清除未使用的单元格。
所示，

我使用这个导入的数据来生成可变长度的报告。 p>

但是如果用户（因为我们需要做的）插入行，那么ActiveSheet.UsedRange将被扩展。这意味着，执行新的查询时，我不能再做一个Ctrl-End来查找数据集中的最后一行。

我可以轻松地清除任何数据与ActiveSheet.UsedRange.Clear。但是，如果先前的查询生成了一个2或3页的报告，即使数据较少，UsedRange仍然指向最后一行，任何后续查询也将会很长。

显示的示例如

  ActiveSheet.UsedRange 
 ActiveSheet.UsedRange.Clear 
a = ActiveSheet.UsedRange.Rows.Count 
  

不要重置范围。

MS将UsedRange定义为readOnly属性。

为了完成操作，出现需要进行的是文件保存。

  ActiveWorkbook.Save 
  

$一个帖子指出，在旧版本的Excel中，您也必须关闭工作簿并重新打开以完成操作。

我想知道
1.什么是该行为更改的版本截止？
2.还有其他一些使用VBA宏的方法可以重新设置范围？

解决方案

确保所有的修补程序和服务包已经安装，并且它们是。

我正在运行
Windows 10和
Excel 2016版本16.0 .6568.2034

我发现这个范围只能用

  ActiveSheet.UsedRange.Clear 
  

最重要的是

  ActiveWorkbook.Save 
  

没有保存命令范围未重置

Don't know what I'm missing, but the examples I see posted do not appear to work.

I import data from a web query. I set the query to clear unused cells when it re-queries.

I used this imported data to generate a report of variable length.

However if the user (as they need to do in my case) insert rows then the ActiveSheet.UsedRange is expanded. This means I cannot any longer just do a "Ctrl-End" to find the last row in the data set when a new query is performed.

I can easily clear any data with ActiveSheet.UsedRange.Clear. However if the previous query generated a 2 or 3 page report any subsequent query will also be that long even when there is less data because the "UsedRange" still points to that last row way down there.

The examples shown like

ActiveSheet.UsedRange
ActiveSheet.UsedRange.Clear
a = ActiveSheet.UsedRange.Rows.Count

do not reset the range.

MS defines UsedRange as a readOnly property.

It appears what needs to happen is a "File Save" in order to complete the action.

ActiveWorkbook.Save

One post noted that in older versions of Excel you also had to close the workbook and reopen it to complete the action.

I would like to know 1. What is the version cutoff where this behavior changed? 2. Is there some other method using a VBA macro which will reset the range?

解决方案

I double checked to make sure all the latests patches and service packs have been installed and they were.

I'm running Windows 10 and Excel 2016 version 16.0.6568.2034

I found that the range would only reset with the

ActiveSheet.UsedRange.Clear

And most importantly

ActiveWorkbook.Save

without the save command the range is not reset

这篇关于Excel重置“UsedRange”的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！