Record count in Excel MARCO VBA

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

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

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

what happens if some one happens to skip a line?

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

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

Opps... sorry.

ping:

Try this

Worksheet.Rows.Count

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

Sorry

Worksheet.Rows.Count

doesn't really work

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

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