计算工作表中的行数

I want to count number of rows in Sheet1, from the Sheet2 code module.

我想从Sheet2代码模块中计算Sheet1中的行数。

In the sheet1 code module, the following code works fine

在sheet1代码模块中，以下代码工作正常

ctr = Range("B2", Range("B2").End(xlDown)).Count

I tried the same code in the Sheet2 code 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

我得到运行时错误1004应用程序 - 定义或定义错误

Thanks

谢谢

4 个解决方案

#1

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

#2

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 Find is easier

为了干净地处理所有情况，然后查找更容易

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

#3

You can use this for example:

您可以使用此示例：

rowsInThere = Sheets("Sheet1").UsedRange.Rows.Count

This works without ranges. Also you might use ActiveSheet as a sheet to check, in case you would need to change current sheet and check its rows count.

这没有范围。您还可以使用ActiveSheet作为工作表来检查，以防您需要更改当前工作表并检查其行数。

#4

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.

如果我知道如果我正在处理的数据集没有空行或列，比如来自另一个程序或其他东西的摘录，那么它很快就会很好用！从这里我可以指定范围选择或执行vlookup。

TR = [Sheet1!A1].CurrentRegion.Rows.count
[I2] = "=vlookup($C2,'sheet1'!A$2:B$" & TR & ",2,FALSE)"

#1