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/

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

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/

.

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/

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/

THANKS Y'ALL! That rocks! - Piku

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