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
that.

thank you very much~ :)




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


nickong wrote:
Hi,all

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?
http://classicvb.org/





.



Relevant Pages

  • Re: Sort key
    ... Sub sheet_name ... Dim SHName As String ... MsgBox "the nameof the second worksheet is" & SHName, ...
    (microsoft.public.excel.programming)
  • 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. ...
    (microsoft.public.excel.programming)
  • 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. ...
    (microsoft.public.excel.worksheet.functions)
  • 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. ...
    (microsoft.public.excel.programming)
  • 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 ...
    (microsoft.public.excel.programming)