We help IT Professionals succeed at work.

Excel Sheet Question

PX
PX asked
on
Hi,

Can someone help me for some codes to know how many records count in a microsoft excel worksheet.

Thx in advance
Comment
Watch Question

Suat OzgurWeb / Application Developer
CERTIFIED EXPERT

Commented:
Easiest and fast way i found is below.

Sub CountRecords()
    Range("A2").Select
    Selection.End(xlDown).Select
    MsgBox ActiveCell.Row - 1
End Sub

Basically, goto A2 and press End then Down Arrow then you are at the last row. Get this row's row number and you have rownumber-1 records (assume you have headers).

If you want better way by using DAO and see your sheet as a database then please let me know to show it you.

regards
suat
PX

Author

Commented:
hi smozgur,

Thanks for ur quick responce, I do want to know the way use DAO to get the record count, plz give me the idea how to do that, thx ^_^
Web / Application Developer
CERTIFIED EXPERT
Commented:
Then use this code

Sub CountRecs()
'Add Microsoft DAO 3.x from Tools_References window
'If you are using an Excel version except 2000 then change
'"Excel 8.0;" to "Excel 5.0;" below
Dim dbmain As Database
Dim rcset As Recordset
    Set dbmain = OpenDatabase(ThisWorkbook.FullName, False, False, "Excel 8.0;")
    Set rcset = dbmain.OpenRecordset("Sheet1$") '$ should be added into the table name
    MsgBox rcset.RecordCount
    Set rcset = Nothing
    Set dbmain = Nothing
End Sub

regards
suat
Suat OzgurWeb / Application Developer
CERTIFIED EXPERT

Commented:
And?
PX

Author

Commented:
Hi smozgur,

It's work very well, thanks for ur help ^_^
Suat OzgurWeb / Application Developer
CERTIFIED EXPERT

Commented:
Thanks for the grade.

suat

Explore More ContentExplore courses, solutions, and other research materials related to this topic.