8

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

In the sheet1 code module, the following code works fine

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

I tried the same code in the Sheet2 code module

recct = ThisWorkbook.Sheets("Sheet1").Range("B2", Range("B2").End(xlDown)).Count

I am getting run time error 1004 Application -Defined or Defined error

Thanks

Community
  • 1
  • 1
ash mehta
  • 89
  • 1
  • 1
  • 2
  • 1
    Thanks Scott Holtzman I have tried your code and it does work. Thanks again. and everyone who has commented. – ash mehta Oct 26 '12 at 14:07
  • You probably got more than you bargained for, ash! But welcome to SO. It's great to be able to learn so much so quickly. Given all the answers below, whichever you feel best suits your needs, please mark it as accepted by clicking the blank check mark under it. This helps others in the future know which best helped you. – Scott Holtzman Oct 26 '12 at 14:25

4 Answers4

9

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.

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

However, if you want to find the last "real" used cell in column B (by real, I mean with data in it) do this:

With ThisWorkbook.Sheets("Sheet1")

    recct = .Range("B2", .Range("B" & .Rows.Count).End(xlUp)).Rows.Count

End With
Scott Holtzman
  • 27,099
  • 5
  • 37
  • 72
  • 1
    Only problems with this is that you'll get 1048575 (Excel 2010) if there's no data after B2, and it ignores the first row. – Daniel Oct 26 '12 at 13:53
  • 2
    @DanielCook -> there is no problem with this code as it solves the users question. Sure there are caveats with using `.End`, but since the user provided the code, I have to assume he knows what he is doing with it. In other words `.End(xlDown)`, can be perfectly valid if you now the dataset you are working with. – Scott Holtzman Oct 26 '12 at 13:55
  • 1
    +1 had overlooked your answer was full referencing `Sheet1` - had you edited your initial post? – brettdj Oct 26 '12 at 13:58
  • 2
    @ScottHoltzman I disagree, I do not think that's what the OP is asking. They indicated what they wanted to do, and then showed what they tried. They didn't say what they tried was the right approach. – Daniel Oct 26 '12 at 14:04
  • @brettdj-> no, I didn't edit for that, but am about to edit it to clear up all the discussion the board about the "real" row counts :) – Scott Holtzman Oct 26 '12 at 14:05
  • @DanielCook -> I edited my post to help clear up all the confusion around how to properly count cells and if you are looking for the cell with the last piece of real data. In the end, it's good to point out all the possible pitfalls -> helps us all grow as programmers :) – Scott Holtzman Oct 26 '12 at 14:08
  • Thanks again. Yes i was not counting rows, was trying to count cells underneath B2 assuming there are no empty cells. thanks – ash mehta Oct 26 '12 at 14:19
  • 1
    @Scott I've resorted back to `Find` given the `xlup` variants have several boundary conditions exacerbated by not working on a full column (ie what if B2 is blank, entire column is blank etc). See http://stackoverflow.com/questions/4872512/last-not-empty-cell-in-row-excel-vba/8583926#8583926 :) – brettdj Oct 26 '12 at 14:21
4

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.

brettdj
  • 54,857
  • 16
  • 114
  • 177
Vesper
  • 18,599
  • 6
  • 39
  • 61
  • 1
    This doesn't answer the question - is is particular to B2 to the last used cell in column B – brettdj Oct 26 '12 at 13:51
  • 2
    you could use that, but if there are blank cells between `Range("B2").End(xlDown)` and the last cell in the `UsedRange` you won't return the row count you expect! – Scott Holtzman Oct 26 '12 at 13:52
  • No, the question is "I want to count number of rows on Sheet1", and this is an answer (an alternate answer, not using his method to check rows). And to Scott, this depends on the data in sheets, and whatever intention does the OP have. I've seen "number of rows" period, I give this as answer. – Vesper Oct 26 '12 at 13:54
  • Also, `UsedRange` can end up picking up cells that don't have actual data, but have leftover or unintentional formatting or empty character spaces. It's simple and easy, but fails too often :) – Scott Holtzman Oct 26 '12 at 14:07
  • 1
    @ScottHoltzman while that can be true , `rowsInThere = Sheets("Sheet1").UsedRange.Rows.Count` forces a `UsedRange` recount. So the count will be accurate. [more here](http://www.j-walk.com/ss/excel/tips/tip73.htm) – brettdj Oct 26 '12 at 14:16
2

Two things

  1. When working off sheet you need to fully qualify your range
  2. 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
brettdj
  • 54,857
  • 16
  • 114
  • 177
1

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$2:B$" & TR & ",2,FALSE)"
BeachBum68
  • 96
  • 9