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

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


---
Message posted from http://www.ExcelForum.com/

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

One way:

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

where n is your starting row.

In article ,
pikus wrote:

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

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

Pikus, Try:

dim lngRowCount as long
lngRowCount = activesheet.usedrange.rows.count

HTH

-----Original Message-----
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


---
Message posted from http://www.ExcelForum.com/

.

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

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("Sheet 1").Range("A:A"))

Alternatively, you could use the SpecialCells method:

MsgBox
Sheets("Sheet1").Range("A:A").SpecialCells(xlCellT ypeConstants).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]


pikus wrote:
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


---
Message posted from http://www.ExcelForum.com/

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

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

--
Regards,
Tom Ogilvy


"pikus" wrote in message
...
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


---
Message posted from http://www.ExcelForum.com/

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

THANKS Y'ALL! That rocks! - Piku

--
Message posted from http://www.ExcelForum.com