vba 计算工作表中的行数
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13088090/
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
Count number of rows in worksheet
提问by ash mehta
I want to count number of rows in Sheet1, from the Sheet2 code module.
我想从 Sheet2 代码模块计算 Sheet1 中的行数。
In the sheet1code module, the following code works fine
在sheet1代码模块中，以下代码工作正常
ctr = Range("B2", Range("B2").End(xlDown)).Count
I tried the same code in the Sheet2code module
我在Sheet2代码模块中尝试了相同的代码
recct = ThisWorkbook.Sheets("Sheet1").Range("B2", Range("B2").End(xlDown)).Count
I am getting run time error 1004 Application -Defined or Defined error
我正进入（状态 run time error 1004 Application -Defined or Defined error
Thanks
谢谢
回答by Scott Holtzman
The error occurs in the 2nd range reference in recct. Because you are referencing a different sheet, you need to tell VBA the sheet name in both range references.
错误发生在recct. 因为您正在引用不同的工作表，所以您需要在两个范围引用中告诉 VBA 工作表名称。
Try this instead:
试试这个：
With ThisWorkbook.Sheets("Sheet1")
recct = .Range("B2", .Range("B2").End(xlDown)).Rows.Count
End With
Alternatively, this will work as well (though a bit sloppier).
或者，这也可以工作（虽然有点马虎）。
recct = ThisWorkbook.Sheets("Sheet1").Range("B2", ThisWorkbook.Sheets("Sheet1").Range("B2").End(xlDown)).Rows.Count
Update
更新
Since there is a lot of discussion around what you actually mean by number of rows on the sheet, use the above code to literally start at B2 and count the number of contiguous cells directly underneath
由于围绕工作表上的行数的实际含义有很多讨论，因此使用上面的代码从字面上开始从 B2 开始并计算正下方的连续单元格的数量
However, if you want to find the last "real" used cell in column B (by real, I mean with data in it) do this:
但是，如果您想在 B 列中找到最后一个“真实”使用的单元格（真实的，我的意思是其中包含数据），请执行以下操作：
With ThisWorkbook.Sheets("Sheet1")
recct = .Range("B2", .Range("B" & .Rows.Count).End(xlUp)).Rows.Count
End With
回答by Vesper
You can use this for example:
例如，您可以使用它：
rowsInThere = Sheets("Sheet1").UsedRange.Rows.Count
This works without ranges. Also you might use ActiveSheetas a sheet to check, in case you would need to change current sheet and check its rows count.
这在没有范围的情况下工作。您也可以ActiveSheet用作工作表进行检查，以防您需要更改当前工作表并检查其行数。
回答by brettdj
Two things
两件事情
- When working off sheet you need to fully qualify your range
- Always measure the last cell bottom up rather than top down - you may have gaps
- 在表外工作时，您需要完全限定您的范围
- 始终自下而上而不是自上而下测量最后一个单元格 - 您可能有间隙
code
代码
Sub GetB()
Dim ws As Worksheet
Set ws = Sheets(1)
Dim lngCnt As Long
lngCnt = ws.Range(ws.[b2], ws.Cells(Rows.Count, "b").End(xlUp)).Count
End Sub
more robust
更健壮
To handle all situations cleanly then Findis easier
干净地处理所有情况然后Find更容易
Sub GetB()
Dim ws As Worksheet
Dim rng1 As Range
Set ws = Sheets(1)
Set rng1 = ws.Range("B:B").Find("*", ws.[b1], xlValues, , , xlPrevious)
If Not rng1 Is Nothing Then
Select Case rng1.Row
Case 1
MsgBox "Only B1 has data", vbCritical
Case 2
MsgBox "No used cells past B2"
Case Else
MsgBox rng1.Row - 1 & " cells between B2 and B" & rng1.Row
End Select
Else
MsgBox ws.Name & " column B Is blank", vbCritical
End If
End Sub
回答by BeachBum68
Don't know if this will help but I use this in my modules all the time:
不知道这是否有帮助，但我一直在我的模块中使用它：
Dim TR as long, TC as long
TR = [Sheet1!A1].CurrentRegion.Rows.count
TC = [Sheet1!A1].CurrentRegion.Columns.count
If I know that if the dataset I'm dealing with doesn't have an empty row or column, like an extract from another program or something, then it's quick and works great! From this I can specify a range select or perform a vlookup.
如果我知道如果我正在处理的数据集没有空行或列，比如来自另一个程序的摘录或其他东西，那么它很快并且效果很好！从此我可以指定范围选择或执行查找。
TR = [Sheet1!A1].CurrentRegion.Rows.count
[I2] = "=vlookup($C2,'sheet1'!A:B$" & TR & ",2,FALSE)"
