CODEWITHSUNDEEP
excelvba
kumar chaudhari
kumar chaudhari

Reputation: 89

Excel file to count total rows in VBA

I am writing a VBA macro to count the number of rows in sheet2 of Excel file

Dim cnt As Integer 
Dim i As Integer

cnt = Range("Sheet2").Rows.Count ' giving error at this row
For i = 2 To cnt
    t1 = "A" + CStr(i)
    t2 = "B" + CStr(i)
Next i

But it giving error:

Method 'Range' of object '_global' failed

Upvotes: 1

Views: 78710

Answers (3)

Colin Broadley
Colin Broadley

Reputation: 1

Try using the following code:

Dim crb1 As Long


'Count Rows

    Range("B2").Select
    Selection.End(xlDown).Select
    crb1 = ActiveCell.Row

    MsgBox ("Number of rows are ") & Str(crb1)

Upvotes: -1

Arun Singh
Arun Singh

Reputation: 1546

Range is used for Cell Range not for Excel Sheets.

Try the followings:

  1. To get the count of used rows:

    cnt = Worksheets("Sheet2").Cells.SpecialCells(xlCellTypeLastCell).Row

  2. To get the count of all rows of the sheet:

    cnt = Worksheets("Sheet2").Rows.Count

  3. To get the count of rows of a specific Range:

    cnt = Worksheets("Sheet2").Range("A1:A6500").Rows.Count

I think you are looking for used cell range. Define variables as Long to resolve the overflow issue

Dim cnt As Long 
Dim i As Long

Upvotes: 5

GSerg
GSerg

Reputation: 78155

Sheet2 is a sheet, not a range.

Worksheets("Sheet2").Rows.Count

Upvotes: 1

Related Questions