Re: Re: How to get the rows count in EXCEL work sheet ??

You should use Karl's formula. Consider a worksheet completely empty except
for ones in A9 and A10.

MsgBox (Sheet1.Cells(Sheet1.Rows.Count, "G").End(xlUp).Row)
will return an incorrect value of 1

MsgBox (ActiveSheet.UsedRange.Rows.Count)
will return the correct value of 2
Gary's Student

"nickong" wrote:

Thank you , Karl

i found another way to do this,

Sheet1.Cells(Sheet1.Rows.Count, "G").End(xlUp).Row

and i think your suggestion ActiveSheet.UsedRange.Rows.Count is better than

thank you very much~ :)

----- Original Message -----
From: "Karl E. Peterson" <karl@xxxxxxxx>
Sent: Friday, November 17, 2006 10:18 AM
Subject: Re: How to get the rows count in EXCEL work sheet ??

nickong wrote:

How to get the rows count in excel worksheet ?

dim i As Integer
i= Sheet1.Rows.Count
i get the number returned: 65536

but my worksheet only contain 5000 rows , how could this happen ?
how can i get the actual count number ?

which funciton should i use ???

Try ActiveSheet.UsedRange.Rows.Count
Working without a .NET?


Relevant Pages

  • Re: Sort key
    ... Sub sheet_name ... Dim SHName As String ... MsgBox "the nameof the second worksheet is" & SHName, ...
  • Re: Newbie question on variables
    ... dim res as boolean ... msgbox "Failed" ... Function myFunc(wks as worksheet) as boolean ... I think it's the wsCert variable. ...
  • Re: look up in work spreadsheet
    ... I like the worksheet changge method because you can have a msgbox tell you ... 24,000 line cell that I have to enter by hand. ... MsgBox ) ... I have included an example on the excel 2003 spreadsheet of what we want. ...
  • Re: "Or" statement not working
    ... Using a worksheet cell to calculate the function works, so I am content, ... macro, but will not necessarily work in a function. ... it will display the MsgBox and exit. ...
  • Re: How can i find and View required values ?
    ... Remember to enclose the worksheet name in apostrophes if required: ... Tom Ogilvy ... Dim v As Variant, i As Long ... I wouldn't see using a msgbox as the final ...