Record count in Excel MARCO VBA

How do I get a record count in Excel???  I just want to know how many records are in a sheet??
Thanks
strongdAsked:
Who is Participating?
 
nfernandCommented:
Hi guys... anothe Idea.

1) Use the CountA function to know how many cells in a range are not empty: =counta(A1:A2000) returns de number of non-empty cells between A1 and A2000.

2) In code... the same thing:

public function RecNumber() as double
RecNumber= Application.WorkSheetFunction.CountA(Range("A1:A2000")
end Function

Bye
0
 
TrueDrakeCommented:
Hi,
Some people use UsedRange but i simply add a function:

Function LastRow()
  For i=1 to 32000
   Cells(i,1).Select
   a = ActiveCell.Value
   if a <> "" then
   LastRow=a-1
   Exit Sub
  Next
End Function

Enjoy
0
 
TrueDrakeCommented:
Hi,
Sorry:
Some people use UsedRange but i simply add a function:

Function LastRow()
 For i=1 to 32000
  Cells(i,1).Select
  a = ActiveCell.Value
  if a <> "" then
     LastRow=a-1
     Exit Function
  End If
 Next
End Function

Enjoy
0
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
TrueDrakeCommented:
Hi,
Bad day:
Some people use UsedRange but I simply add a function:

Function LastRow()
  For i=1 to 32000
   Cells(i,1).Select
   a = ActiveCell.Value
   If a = "" then
    LastRow=a-1
    Exit Function
   End If
  Next
End Function

Enjoy
0
 
jrspanoCommented:
what happens if some one happens to skip a line?
0
 
nfernandCommented:
Hi guys... anothe Idea.

1) Use the CountA function to know how many cells in a range are not empty: =counta(A1:A2000) returns de number of non-empty cells between A1 and A2000.

2) In code... the same thing:

public function RecNumber() as double
RecNumber= Application.WorkSheetFunction.CountA(Range("A1:A2000")
end Function

Bye
0
 
nfernandCommented:
Hi guys... anothe Idea.

1) Use the CountA function to know how many cells in a range are not empty: =counta(A1:A2000) returns de number of non-empty cells between A1 and A2000.

2) In code... the same thing:

public function RecNumber() as double
RecNumber= Application.WorkSheetFunction.CountA(Range("A1:A2000")
end Function

Bye
0
 
nfernandCommented:
Opps... sorry.
0
 
chandukbCommented:
ping:
0
 
Hornet241Commented:
Try this

Worksheet.Rows.Count
0
 
TrueDrakeCommented:
Hi,


Worksheet.Rows.Count gives 65,536 and is incorrect

public function RecNumber() as double
RecNumber= Application.WorkSheetFunction.CountA(Range("A1:A2000")
end Function

is also OK

Enjoy
0
 
Hornet241Commented:
Sorry

Worksheet.Rows.Count

doesn't really work
0
 
Richie_SimonettiIT OperationsCommented:
If you have for sure that blanks rows are not allowed, try this:

Function CountRecs() As Long

Range("A1").Select
Range("A1", Range("A1").End(xlDown)).Select
'MsgBox Selection.Cells.Count
CountRecs = Selection.Cells.Count

End Function
0
 
Richie_SimonettiIT OperationsCommented:
With this solution, you need to hard-code the range to count.
Cheers
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.