vba Excel 重置“UsedRange”

Excel resetting "UsedRange"

提问by Claus

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.

但是，如果用户（在我的情况下他们需要这样做）插入行，则 ActiveSheet.UsedRange 将展开。这意味着当执行新查询时，我不能再只执行“Ctrl-End”来查找数据集中的最后一行。

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.

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

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.

MS 将 UsedRange 定义为只读属性。

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.

一篇文章指出，在旧版本的 Excel 中，您还必须关闭工作簿并重新打开它才能完成操作。

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?

我想知道 1. 此行为更改的版本截止日期是多少？2. 是否有其他方法使用 VBA 宏来重置范围？

采纳答案by Claus

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

我正在运行 Windows 10 和 Excel 2016 版本 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

没有保存命令，范围不会重置

回答by Monosabio

Best code that worked for me:

对我有用的最佳代码：

Sub DeleteUnused()
Dim myLastRow As Long
Dim myLastCol As Long
Dim dummyRng As Range
Dim AnyMerged As Variant
'http://www.contextures.on.ca/xlfaqApp.html#Unused
'Helps to reset the usedrange by deleting rows and columns AFTER your true used range

    'Check for merged cells
    AnyMerged = ActiveSheet.UsedRange.MergeCells
    If AnyMerged = True Or IsNull(AnyMerged) Then
        MsgBox "There are merged cells on this sheet." & vbCrLf & _
               "The macro will not work with merged cells.", vbOKOnly + vbCritical, "Macro will be Stopped"
        Exit Sub
    End If

    With ActiveSheet
        myLastRow = 0
        myLastCol = 0
        Set dummyRng = .UsedRange
        On Error Resume Next
        myLastRow = _
        .Cells.Find("*", after:=.Cells(1), _
                    LookIn:=xlFormulas, lookat:=xlWhole, _
                    searchdirection:=xlPrevious, _
                    searchorder:=xlByRows).Row
        myLastCol = _
        .Cells.Find("*", after:=.Cells(1), _
                    LookIn:=xlFormulas, lookat:=xlWhole, _
                    searchdirection:=xlPrevious, _
                    searchorder:=xlByColumns).Column
        On Error GoTo 0

        If myLastRow * myLastCol = 0 Then
            .Columns.Delete
        Else
            .Range(.Cells(myLastRow + 1, 1), _
                   .Cells(.Rows.Count, 1)).EntireRow.Delete
            .Range(.Cells(1, myLastCol + 1), _
                   .Cells(1, .Columns.Count)).EntireColumn.Delete
        End If
    End With

End Sub

回答by sirplus

1. select cell 1,1 in any sheets you want to reset the UsedRange property
2. Calculate all worksheets in all open workbooks, regardless of whether they changed since last calculation (To Calculate Fully Ctrl+Alt+F9)
3. Save the workbook

1. 在要重置 UsedRange 属性的任何工作表中选择单元格 1,1
2. 计算所有打开的工作簿中的所有工作表，无论它们自上次计算以来是否发生变化（完全计算 Ctrl+Alt+F9）
3. 保存工作簿

Works for me on all versions of excel

适用于所有版本的 excel

回答by Harley

I only needed to use Worksheets("Sheet1").UsedRange.Calculateafter deleting rows to reset the range.

我只需要 在删除行后使用Worksheets("Sheet1").UsedRange.Calculate来重置范围。

回答by Joe Cross

Thanks to Claus for having the correct answer, but it is incomplete, and he completed it with a comment on the main post. I'll answer here to combine the useful bits into a single working solution.

感谢 Claus 给出了正确的答案，但它不完整，他在主贴上发表了评论。我将在这里回答将有用的部分组合成一个单一的工作解决方案。

Note: I have not tried variations to see which steps are necessary, but these steps work to clear unnecessary used range.

注意：我没有尝试变体来查看哪些步骤是必要的，但这些步骤可以清除不必要的使用范围。

Sub ResetUsedRange()
dim rngToDelete as range

set rngToDelete = 'Whatever you want

rngToDelete.EntireRow.Clear
rngToDelete.EntireRow.Select
Selection.Delete Shift:=xlUp

ActiveWorkbook.Save 'This step has been stated to be necessary for the range to reset.

End Sub

回答by AzzaClazza

This is the solution I used.

这是我使用的解决方案。

Sub CorrectUsedRange()
    Dim values
    Dim usedRangeAddress As String
    Dim r As Range
    'Get UsedRange Address prior to deleting Range
    usedRangeAddress = ActiveSheet.UsedRange.Address
    'Store values of cells to array.
    values = ActiveSheet.UsedRange
    'Delete all cells in the sheet
    ActiveSheet.Cells.Delete
    'Restore values to their initial locations
    Range(usedRangeAddress) = values
End Sub

回答by Travis Dawson

This may or may not suit your data needs, but if your data is all in one contiguous block, you can use CurrentRegion instead of UsedRange, like this:

这可能适合也可能不适合您的数据需求，但如果您的数据都在一个连续的块中，您可以使用 CurrentRegion 而不是 UsedRange，如下所示：

With Cells(1, 1).CurrentRegion
 MsgBox "I have " & .Rows.Count & " rows and " & .Columns.Count & " columns of data."
End With

Of course, if the region you care about does not start at cell A1, or if your sheet contains multiple contiguous regions that you care about, this option will not work. Depending on how predictable your data is, you can usually find at least one cell in each block of data, and once you have that, CurrentRegion will give you the range of the entire block.

当然，如果您关心的区域不是从单元格 A1 开始，或者您的工作表包含您关心的多个连续区域，则此选项将不起作用。根据数据的可预测性，您通常可以在每个数据块中找到至少一个单元格，一旦找到，CurrentRegion 将为您提供整个数据块的范围。

回答by user9127093

I've used Jeeped solution and worked for me when i add .Activate, so:

我使用过 Jeeped 解决方案并在添加 .Activate 时对我来说有效，所以：

With Worksheets("Sheet1")
        Debug.Print .UsedRange.Address(0, 0)
        .UsedRange.Clear
        .UsedRange   
        .Activate
        Debug.Print .UsedRange.Address(0, 0)
  End With

I'm using Excel2013

我正在使用 Excel2013

回答by user9127093

If you call the Worksheet.UsedRange propertyby itself, it will reset.

如果您单独调用Worksheet.UsedRange 属性，它将重置。

    With Worksheets("Sheet1")
        Debug.Print .UsedRange.Address(0, 0)
        .UsedRange.Clear
        .UsedRange    '<~~ called by itself will reset it
        Debug.Print .UsedRange.Address(0, 0)
    End With

This extra step is unnecessary in xl2010 and above with all appropriate service packs installed.

在安装了所有相应服务包的 xl2010 及更高版本中，不需要此额外步骤。

回答by Claus

Here is how I inserted your code.

这是我插入您的代码的方式。

    Sheets("Edit Data").Select
'    Range("A6").Select
'    Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
'    Selection.Delete Shift:=xlUp
'    ActiveWorkbook.Save
    With Worksheets("Edit Data")
        Debug.Print .UsedRange.Address(0, 0)
        .UsedRange.Clear
        .UsedRange    '<~~ called by itself will reset it
        Debug.Print .UsedRange.Address(0, 0)
    End With

Here is the full used range with data

这是数据的完整使用范围

Here is the used range after your code executed

这是代码执行后使用的范围

The end of range should be i7 instead it is still i26

范围的结束应该是 i7 而它仍然是 i26

However the code which I commented out does reset range to i7

但是，我注释掉的代码确实将范围重置为 i7

From what you are saying just to confirm. My commented out code will only work for Excel 2010 and newer. We have some 2007 versions hanging around. For those the workbook will actually have to be closed and reopened for the range to reset?

从你所说的只是为了确认。我注释掉的代码仅适用于 Excel 2010 及更新版本。我们有一些 2007 版本。对于那些工作簿实际上必须关闭并重新打开才能重置范围？

Note- the code examples were executed on version 2016

注意 - 代码示例是在 2016 版上执行的

回答by CyberNot

This works for me in Excel 2010:

这在 Excel 2010 中对我有用：

Worksheets("Sheet1").UsedRange.Clear  
Worksheets("Sheet1").UsedRange.Calculate