Record Count (There has to be an easier way...)

  • Thread starter Thread starter pikus
  • Start date Start date
P

pikus

Up until now I've been counting the number of records in a particular
sheet with this:

Do
x = x + 1
Loop Until Worksheets(1).Cells(x, 1).Value = ""

This seems like the long way to do this. And it only runs until it
hits a blank cell. Is there an easier way to do this that preferably
won't stop at the first blank line? - Pikus
 
One way:

Dim nCount As Long
nCount = Range(Cells(n, 1), Cells(Rows.Count, 1).End(xlUp)).Count

where n is your starting row.
 
Hi pikus,

You can use the Worksheet function COUNTA from VBA to get the count of
non-blank cells in a range:

MsgBox
Application.WorksheetFunction.CountA(Sheets("Sheet1").Range("A:A"))

Alternatively, you could use the SpecialCells method:

MsgBox
Sheets("Sheet1").Range("A:A").SpecialCells(xlCellTypeConstants).Cells.Count

[The latter method assumes the cells you wish to count are constants and not
formulas.]

--
Regards,

Jake Marx
MS MVP - Excel
www.longhead.com

[please keep replies in the newsgroup - email address unmonitored]
 
msgbox Columns(1).SpecialCells(xlConstants).Count

if the cells have formulas xlFormulas rather than xlConstants

will count the filled cells. If you want to get the last row

msgbox cells(rows.count,1).End(xlup).Row
 
Back
Top