ActiveSheet.UsedRange.Rows.count每次运行都会给出错误且不同的结果 [英] ActiveSheet.UsedRange.Rows.count giving wrong and different result on each run

问题描述

我的工作表有29行和39列.目前，我使用

My Worksheet has 29 rows and 39 columns. Currently I use

lrow = ActiveSheet.UsedRange.Rows.count ->用于获取已用行数
lColumn = ActiveSheet.UsedRange.Columns.count ->用于获取已用列数

lrow = ActiveSheet.UsedRange.Rows.count --> for getting used rows count
lColumn = ActiveSheet.UsedRange.Columns.count --> for getting used columns count

Excel每次运行都会给出错误的计数.有时会给出:
行:29列:784
在其他运行中，它给出
行:32755，列为:784
而在其他运行中，它给出不同的值.

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.

我检查了29行和39列之后是否没有垃圾数据.还，在填充数据之前，我用以下代码清除工作表: ActiveWorkbook.Worksheets("Field Difference").Cells.Delete

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

我希望 ActiveWorkbook.Worksheets("Field Difference").Cells.Delete 完全清除工作表，并清除工作表中的垃圾数据(如果有的话).我如何才能确保工作表中没有垃圾数据.

I hope ActiveWorkbook.Worksheets("Field Difference").Cells.Delete completely 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").UsedRange.ClearContents -仅清除内容
ActiveWorkbook.Worksheets("Field Difference").UsedRange.Clear -也清除格式.

I do understand that we have other Options such as:
ActiveWorkbook.Worksheets("Field Difference").UsedRange.ClearContents - to clear only contents
ActiveWorkbook.Worksheets("Field Difference").UsedRange.Clear - to clear formatting as well.

请务必让我知道为什么我对行和列的计数得到错误的值以及解决方法.我可以使用任何其他可靠的方法来获取 UsedRange 行计数和 UsedRange 列计数.

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 way to get the UsedRange row count and UsedRange columns count.

推荐答案

对于列A中的最后一行和列:

For Last Row and Column in Column 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

这篇关于ActiveSheet.UsedRange.Rows.count每次运行都会给出错误且不同的结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！