vba ActiveSheet.UsedRange.Rows.count 在每次运行时给出错误和不同的结果
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/44674413/
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
ActiveSheet.UsedRange.Rows.count giving wrong and different result on each run
提问by sid
My Worksheet has 29 rows and 39 columns. Currently I use
我的工作表有 29 行和 39 列。目前我使用
lrow = ActiveSheet.UsedRange.Rows.count--> for getting used rows countlColumn = ActiveSheet.UsedRange.Columns.count--> for getting used columns count
lrow = ActiveSheet.UsedRange.Rows.count--> 用于获取已用行数lColumn = ActiveSheet.UsedRange.Columns.count--> 用于获取已用列数
Excel gives wrong count every time it runs. Sometimes it gives:
Rows: 29 Columns: 784
On other runs it gives
Rows: 32755 and Columns as: 784
and on other runs it gives different values.
Excel 每次运行时都会给出错误的计数。有时它给出：
Rows: 29 Columns: 784
在其他运行中它给出
Rows: 32755 和 Columns as: 784
而在其他运行中它给出不同的值。
I have checked there is no junk data after 29 rows and after 39 columns. Also,
Previous to filling the data I clear the sheet with: ActiveWorkbook.Worksheets("Field Difference").Cells.Delete
我已经检查过 29 行和 39 列之后没有垃圾数据。此外，在填充数据之前，我使用以下命令清除工作表：ActiveWorkbook.Worksheets("Field Difference").Cells.Delete
I hope ActiveWorkbook.Worksheets("Field Difference").Cells.Deletecompletely clears the sheet and clears the sheet of the junk data if any on the sheet. How else I can make sure that there is no junk data in the worksheet.
我希望ActiveWorkbook.Worksheets("Field Difference").Cells.Delete完全清除工作表并清除工作表上的垃圾数据表（如果有的话）。我还能如何确保工作表中没有垃圾数据。
I do understand that we have other Options such as:ActiveWorkbook.Worksheets("Field Difference").UsedRange.ClearContents- to clear only contentsActiveWorkbook.Worksheets("Field Difference").UsedRange.Clear- to clear formatting as well.
我知道我们还有其他选项，例如：ActiveWorkbook.Worksheets("Field Difference").UsedRange.ClearContents- 仅清除内容ActiveWorkbook.Worksheets("Field Difference").UsedRange.Clear- 也清除格式。
Please do let me know why I am getting wrong values for the count of rows and columns and what is the way out. Can I use any other reliable wayto get the UsedRangerow count and UsedRangecolumns count.
请让我知道为什么我得到错误的行数和列数值以及出路是什么。我可以使用任何其他可靠的方法来获取UsedRange行数和UsedRange列数吗？
回答by UGP
For Last Row and Column in Column A:
对于 A 列中的最后一行和最后一列：
Dim sht as Worksheets
dim lRow as Long
Set sht = Worksheets("Field Difference")
lRow = sht.Cells(sht.Rows.Count, 1).End(xlUp).Row
lCol = sht.Cells(1, sht.Columns.Count).End(xlToLeft).Column
回答by A.S.H
Methods for finding the last populated cell for a givencolumn(row) are well know. using End(xlUp)(End(xlToLeft)) from the last cell of that column (row).
为给定的列（行）查找最后一个填充单元格的方法是众所周知的。从该列（行）的最后一个单元格使用End(xlUp)( End(xlToLeft))。
To get the last cell of the actually populated regionof a worksheet, you can use this custom function which will get it to you reliably:
要获取工作表实际填充区域的最后一个单元格，您可以使用此自定义函数，它将可靠地提供给您：
Public Function getLastCell(sh As Worksheet) As Range
Dim lastRow As Long, lastCol As Long
lastRow = sh.Cells.Find("*", sh.Cells(1, 1), xlFormulas, xlPart, xlByRows, xlPrevious).Row
lastCol = sh.Cells.Find("*", sh.Cells(1, 1), xlFormulas, xlPart, xlByColumns, xlPrevious).Column
Set getLastCell = sh.Cells(lastRow, lastCol)
End Function
By bringing the last cell, you can get the whole range from A1to that cell, using
通过带来最后一个单元格，您可以获得从A1该单元格到该单元格的整个范围，使用
mysheet.Range("A1", getLastCell(mySheet))
Occasionally you might be interested in finding the populated region which is not starting at A1. You can, in this case, find Similarly the "Topleft" Cell of the actually populated region, using this custom function:
有时，您可能对查找不是从 开始的人口稠密地区感兴趣A1。在这种情况下，您可以使用此自定义函数类似地找到实际填充区域的“左上角”单元格：
Public Function getFirstCell(sh As Worksheet) As Range
Dim lastRow As Long, lastCol As Long
lastRow = sh.Cells.Find("*", sh.Cells(sh.Rows.Count, sh.Columns.Count), xlFormulas, xlPart, xlByRows, xlNext).Row
lastCol = sh.Cells.Find("*", sh.Cells(sh.Rows.Count, sh.Columns.Count), xlFormulas, xlPart, xlByColumns, xlNext).Column
Set getFirstCell = sh.Cells(lastRow, lastCol)
End Function
finally, you can jointhe two cells to get the actually ppulated region, like this:
最后，您可以加入两个单元，以获得实际ppulated区域，如下所示：
mysheet.Range(getFirstCell(mySheet), getLastCell(mySheet))
回答by A.S.H
Worksheet layout can affect the .UsedRange and .CurrentRegion properties. For a definitive 'last cell' search backwards from A1 first by rows then by columns using a blanket wildcard.
工作表布局会影响 .UsedRange 和 .CurrentRegion 属性。对于明确的“最后一个单元格”，首先使用一揽子通配符按行然后按列从 A1 向后搜索。
dim lr as long, lc as long
with worksheets("sheet1")
lr = .cells.find(what:=chr(42), after:=.cells(1), searchdirection:=xlprevious, _
lookat:=xlpart, searchorder:=xlbyrows, lookin:=xlformulas).row
lc = .cells.find(what:=chr(42), after:=.cells(1), searchdirection:=xlprevious, _
lookat:=xlpart, searchorder:=xlbycolumns, lookin:=xlformulas).column
debug.print .cells(lr, lc).address(0, 0)
end with
