Geek Answers Handbook

Count the number of rows per sheet

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

In the code1 module of the sheet, the following code works fine

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

I tried the same code in Sheet2 code module

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

I get run time error 1004 Application -Defined or Defined error

thanks

+6
source share
4 answers

An error occurs in the 2nd range assignment in recct . Since you are linking to another 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 (albeit 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 about what you really mean by the number of rows per sheet, use the code above to literally start with B2 and count the number of adjacent cells directly under

However, if you want to find the last “real” used cell in column B (essentially, I mean with the data in it), follow these steps:

 With ThisWorkbook.Sheets("Sheet1") recct = .Range("B2", .Range("B" & .Rows.Count).End(xlUp)).Rows.Count End With
+9
source

You can use this, for example:

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

This works without ranges. You can also use ActiveSheet as a check sheet if you need to change the current sheet and check its row count.

+2
source

Two things

  • When working out a sheet, you need to fully determine your range.
  • Always measure it from bottom to top, not top to bottom - you may have spaces

the 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 reliable

To handle all situations cleanly, then Find 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
+2
source

I don’t know if this will help, but I use it all the time in my modules:

 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 does not contain an empty row or column, for example, extracting from another program or something like that, then this works quickly and perfectly! From this, I can specify the range of choice or do vlookup.

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

More articles:


All Articles