Counting Rows给出了错误的答案 [英] Counting Rows give back wrong answer

问题描述

我道歉但我不会说流利的英语。

I apologize but I don't speak fluenty english.

这是问题所在：

使用VBA的Excel，我想用语法计算行数：" ActiveSheet.UsedRange.Rows.Count"

With VBA's Excel, I want to count the number of rows with the syntax : "ActiveSheet.UsedRange.Rows.Count"

在我的Excel表格中，我有1583行。 VBA sybtax的返回给出：2208。

In my Excel's sheet I Have 1583 rows. The return of the VBA sybtax gives : 2208.

我的所有行，从1584到2208，空单元格

I have all of my rows, from 1584 to 2208 with empty cells

经过几次几个小时，似乎给单元格的格式返回给我一个不好的数字。

After a couple of hours, it seems that the format given to thoses cells return to me a bad number.

实际上如果我给这些单元格格式为"标准"。我可以获得实际的行数。

In fact If I give to those cells the format "Standard" I can get the real number of rows.

所以我的问题：

1-我是否必须将此视为错误？

1- Do I have to consider this as a bug ?

2-如何防止或计算我的用户工作表中的行数，这些行在空行中有很多不同格式的单元格？

2- How to prevent, or count well the numbers of rows in the sheets of my users which have a lot of different format's cell in empty rows ?

包括包含任何格式的行和列，即使是空的。尝试以下

The UsedRange includes rows and columns that contain any formatting, even if empty. Try the following

Sub test()

    MsgBox LastRow(ActiveSheet, 0) ' maximum last row of all columns in the sheet
    
    MsgBox LastRow(ActiveSheet, 1) ' last row in col-1

End Function

函数LastRow（ ws As Worksheet，可选col as Long）As long
Dim lr As Long
LastRow = 1
使用ws
如果col = 0则
lr = .Cells。查找（"*"，。。范围（"A1"），xlFormulas，xlPart，xlByRows，xlPrevious，0）。行
否则
lr = .Cells（.Rows.Count，col） .End（xlUp）.Row
结束如果
LastRow = lr
结束时
退出函数
errExit：
'可能是空单
结束功能

Function LastRow(ws As Worksheet, Optional col As Long) As Long Dim lr As Long LastRow = 1 With ws If col = 0 Then lr = .Cells.Find("*", .Range("A1"), xlFormulas, xlPart, xlByRows, xlPrevious, 0).Row Else lr = .Cells(.Rows.Count, col).End(xlUp).Row End If LastRow = lr End With Exit Function errExit: ' probably empty sheet End Function

这篇关于Counting Rows给出了错误的答案的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！