工作表中的最后使用的单元格 [英] Last Used Cell in sheet

问题描述

我是这里的新手，我希望使用Excel VBA返回工作表中最后使用的单元格.

I'm new here and I'm looking to use Excel VBA to return the last used cell in a worksheet.

我看了在Excel中找到最后使用的单元格时出错使用VBA )，但无法解决我遇到的两个问题:

I'vv looked at Error in finding last used cell in Excel with VBA) but that didn't answer the two problems I have:

1. .Cells.Find(...).Row方法在我的代码中花费的时间太长.

1. The .Cells.Find(...).Row method takes WAY too long in my code.

最后使用的电池"的含义是可能很奇怪...单元格可以为空.我想获取具有最后使用单元格的列，并将其与具有最后使用单元格的行配对.

My meaning of "last used cell" might be odd...the cell can be blank. I want to take the column that has the last used cell and pair it with the row that has the last used cell.

说明:假定除了A1:C3，D2和B4中的数据外，工作表为空. (图1 )

To explain: Assume a sheet is empty except for data in A1:C3, D2 and B4. (Exhibit 1)

我感兴趣的最后一个单元格是D4，因为我希望工作表中的最后一个单元格包含工作表中的所有数据.

The last cell I'm interested in is D4because I want the last cell in the sheet that includes all data in the sheet.

现在我已经解释了我要寻找的东西，任何人都可以提供有关其中任何一个的建议

Now that I've explained what I'm looking for, can anyone provide suggestions as to either

• 如何使cell.find运行得更快或
另一可靠的方法是找到最后一个细胞".在工作表中?

• how to make cells.find run faster or
• another credible method to find the "last cell" in a worksheet?

谢谢！

推荐答案

您尝试过吗?

Dim r As Range
Set r = Sheet1.UsedRange.SpecialCells(xlCellTypeLastCell)
Debug.Print r.Address

您的示例的输出:

$ D $ 4

$D$4

已知UsedRange并不总是与实际使用数据范围匹配.一些解决方法是使用CurrentRegion:

The UsedRange is known to not always match the actually used data range. Some workaround would be to use CurrentRegion:

Dim r As Range
With Sheet1.Range("A1").CurrentRegion
    Set r = Sheet1.Cells(.Rows.count, .Columns.count)
End With
Debug.Print r.Address

如果数据不是从A1开始，也可能是这样:

also if the data does not start at A1, maybe this:

With Sheet1.Cells.Find("*").CurrentRegion

这篇关于工作表中的最后使用的单元格的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！