Solved

# Record count in Excel MARCO VBA

Posted on 2001-06-20
543 Views
How do I get a record count in Excel???  I just want to know how many records are in a sheet??
Thanks
0
Question by:strongd
• 4
• 4
• 2
• +3

LVL 2

Expert Comment

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

LVL 2

Expert Comment

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

LVL 2

Expert Comment

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 Function
End If
Next
End Function

Enjoy
0

LVL 3

Expert Comment

what happens if some one happens to skip a line?
0

LVL 2

Expert Comment

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

LVL 2

Expert Comment

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

LVL 2

Accepted Solution

nfernand earned 50 total points
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

LVL 2

Expert Comment

Opps... sorry.
0

LVL 2

Expert Comment

ping:
0

LVL 3

Expert Comment

Try this

Worksheet.Rows.Count
0

LVL 2

Expert Comment

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

LVL 3

Expert Comment

Sorry

Worksheet.Rows.Count

doesn't really work
0

LVL 16

Expert Comment

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

LVL 16

Expert Comment

With this solution, you need to hard-code the range to count.
Cheers
0

## Featured Post

Enums (shorthand for ‘enumerations’) are not often used by programmers but they can be quite valuable when they are.  What are they? An Enum is just a type of variable like a string or an Integer, but in this case one that you create that contains…
You can of course define an array to hold data that is of a particular type like an array of Strings to hold customer names or an array of Doubles to hold customer sales, but what do you do if you want to coordinate that data? This article describes…
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…