How to get actual rows number in Excel using VBA?

Hi all,
Now I have a excel file, in sheet1,
the actual rows number is 20, that is, only the first 20 rows are
using.
But I use Activesheet.usedrange.rows.count, it gets 26, and sometimes
more.
But there's no content in those rows.
Why?
Thanks.

michael

That is the problem with used range. You get 26 because at some point there
were 26 or more rows of data. If you were to delete all of the rows below 20
and then save the file, now the used range will be back down to 20. The most
accurate way is to use code such as this

msgbox cells(rows.count, "A").end(xlup).row

This will give you the last row in column A with anything in it...
--
HTH...

Jim Thomlinson


"[email protected]" wrote:

> Hi all,
> Now I have a excel file, in sheet1,
> the actual rows number is 20, that is, only the first 20 rows are
> using.
> But I use Activesheet.usedrange.rows.count, it gets 26, and sometimes
> more.
> But there's no content in those rows.
> Why?
> Thanks.
>
> michael
>
>

Jim's results work as long as the column being tested is never empty. There
is usually one such column.

Here is the solution that I use and it will work as long as formatting has
not been applied to rows below the bottom row. This is normally the case
when VBA has created the worksheet.

Activesheet.UsedRange.Row - 1 + Activesheet.UsedRange.Rows.Count

To find the last column use:
Activesheet.UsedRange.Column - 1 +
Activesheet.UsedRange.Columns.Count



ActiveSheet.UsedRange.Rows.Count by itself should also work if every cell in
the row has data.

The difference between
ActiveSheet.UsedRange.Rows.Count
and
Activesheet.UsedRange.Row - 1 + Activesheet.UsedRange.Rows.Count

can be seen by creating a worksheet and entering data in column A for the
first 5 rows. Both formulas will return 5. Now, insert two blank rows at
the top of the list. Thus, data will exist in rows 3 through 7.
My formulas will return 7 but Rows.Count will only return 5.

One more test. Change the cell color of the first empty row (row 8) and
rerun. My formula will return 8 and Rows.Count will return 14. In this
case, Jim's method will yield the correct results.


--
My handle should tell you enough about me. I am not an MVP, expert, guru,
etc. but I do like to help.


"Jim Thomlinson" <[email protected]> wrote in message
news:[email protected]...
> That is the problem with used range. You get 26 because at some point
> there
> were 26 or more rows of data. If you were to delete all of the rows below
> 20
> and then save the file, now the used range will be back down to 20. The
> most
> accurate way is to use code such as this
>
> msgbox cells(rows.count, "A").end(xlup).row
>
> This will give you the last row in column A with anything in it...
> --
> HTH...
>
> Jim Thomlinson
>
>
> "[email protected]" wrote:
>
>> Hi all,
>> Now I have a excel file, in sheet1,
>> the actual rows number is 20, that is, only the first 20 rows are
>> using.
>> But I use Activesheet.usedrange.rows.count, it gets 26, and sometimes
>> more.
>> But there's no content in those rows.
>> Why?
>> Thanks.
>>
>> michael
>>
>>

In the case of the original post, your method would return 26 and the OP
wants 20. Your assumption was that UsedRange was not reporting different
from perception. Yet that assumes away the Problem the OP is having.


In any event, UsedRange is always correct. It just happens to be that
people misinterpret what UsedRange actually is. It doesn't pertain to the
extent of the cells containing data although it will always include the
rectangular area that covers those as a minimum. It actually states the
extent of the cells Excel is physically storing information pertaining to
(other cells are vitual until used). This can often over state what we
perceive it should be - but that is our error in trying to use it for
something for which it is not intended and then whining when it doesn't match
our faulty expectations.

--
Regards,
Tom Ogilvy



"AnExpertNovice" wrote:

> Jim's results work as long as the column being tested is never empty. There
> is usually one such column.
>
> Here is the solution that I use and it will work as long as formatting has
> not been applied to rows below the bottom row. This is normally the case
> when VBA has created the worksheet.
>
> Activesheet.UsedRange.Row - 1 + Activesheet.UsedRange.Rows.Count
>
> To find the last column use:
> Activesheet.UsedRange.Column - 1 +
> Activesheet.UsedRange.Columns.Count
>
>
>
> ActiveSheet.UsedRange.Rows.Count by itself should also work if every cell in
> the row has data.
>
> The difference between
> ActiveSheet.UsedRange.Rows.Count
> and
> Activesheet.UsedRange.Row - 1 + Activesheet.UsedRange.Rows.Count
>
> can be seen by creating a worksheet and entering data in column A for the
> first 5 rows. Both formulas will return 5. Now, insert two blank rows at
> the top of the list. Thus, data will exist in rows 3 through 7.
> My formulas will return 7 but Rows.Count will only return 5.
>
> One more test. Change the cell color of the first empty row (row 8) and
> rerun. My formula will return 8 and Rows.Count will return 14. In this
> case, Jim's method will yield the correct results.
>
>
> --
> My handle should tell you enough about me. I am not an MVP, expert, guru,
> etc. but I do like to help.
>
>
> "Jim Thomlinson" <[email protected]> wrote in message
> news:[email protected]...
> > That is the problem with used range. You get 26 because at some point
> > there
> > were 26 or more rows of data. If you were to delete all of the rows below
> > 20
> > and then save the file, now the used range will be back down to 20. The
> > most
> > accurate way is to use code such as this
> >
> > msgbox cells(rows.count, "A").end(xlup).row
> >
> > This will give you the last row in column A with anything in it...
> > --
> > HTH...
> >
> > Jim Thomlinson
> >
> >
> > "[email protected]" wrote:
> >
> >> Hi all,
> >> Now I have a excel file, in sheet1,
> >> the actual rows number is 20, that is, only the first 20 rows are
> >> using.
> >> But I use Activesheet.usedrange.rows.count, it gets 26, and sometimes
> >> more.
> >> But there's no content in those rows.
> >> Why?
> >> Thanks.
> >>
> >> michael
> >>
> >>
>
>
>

My thought is that rows below the 20 are formatted in someway and thus are
part of the UsedRange. That is why the test showing the formatted cell was
included. While ActiveSheet.UsedRange "releases" lines from the bottom of
the range (as shown by a using Ctrl-End) it does not "release" lines that
have been formatted in some way.

If there is a possibility of that then Jim's method works.

In this case, I was merely trying to showi the poster ways to "see" why the
differences exist.

--
My handle should tell you enough about me. I am not an MVP, expert, guru,
etc. but I do like to help.


"Tom Ogilvy" <[email protected]> wrote in message
news:[email protected]...
> In the case of the original post, your method would return 26 and the OP
> wants 20. Your assumption was that UsedRange was not reporting different
> from perception. Yet that assumes away the Problem the OP is having.
>
>
> In any event, UsedRange is always correct. It just happens to be that
> people misinterpret what UsedRange actually is. It doesn't pertain to the
> extent of the cells containing data although it will always include the
> rectangular area that covers those as a minimum. It actually states the
> extent of the cells Excel is physically storing information pertaining to
> (other cells are vitual until used). This can often over state what we
> perceive it should be - but that is our error in trying to use it for
> something for which it is not intended and then whining when it doesn't
> match
> our faulty expectations.
>
> --
> Regards,
> Tom Ogilvy
>
>
>
> "AnExpertNovice" wrote:
>
>> Jim's results work as long as the column being tested is never empty.
>> There
>> is usually one such column.
>>
>> Here is the solution that I use and it will work as long as formatting
>> has
>> not been applied to rows below the bottom row. This is normally the case
>> when VBA has created the worksheet.
>>
>> Activesheet.UsedRange.Row - 1 + Activesheet.UsedRange.Rows.Count
>>
>> To find the last column use:
>> Activesheet.UsedRange.Column - 1 +
>> Activesheet.UsedRange.Columns.Count
>>
>>
>>
>> ActiveSheet.UsedRange.Rows.Count by itself should also work if every cell
>> in
>> the row has data.
>>
>> The difference between
>> ActiveSheet.UsedRange.Rows.Count
>> and
>> Activesheet.UsedRange.Row - 1 + Activesheet.UsedRange.Rows.Count
>>
>> can be seen by creating a worksheet and entering data in column A for the
>> first 5 rows. Both formulas will return 5. Now, insert two blank rows
>> at
>> the top of the list. Thus, data will exist in rows 3 through 7.
>> My formulas will return 7 but Rows.Count will only return 5.
>>
>> One more test. Change the cell color of the first empty row (row 8) and
>> rerun. My formula will return 8 and Rows.Count will return 14. In this
>> case, Jim's method will yield the correct results.
>>
>>
>> --
>> My handle should tell you enough about me. I am not an MVP, expert,
>> guru,
>> etc. but I do like to help.
>>
>>
>> "Jim Thomlinson" <[email protected]> wrote in
>> message
>> news:[email protected]...
>> > That is the problem with used range. You get 26 because at some point
>> > there
>> > were 26 or more rows of data. If you were to delete all of the rows
>> > below
>> > 20
>> > and then save the file, now the used range will be back down to 20. The
>> > most
>> > accurate way is to use code such as this
>> >
>> > msgbox cells(rows.count, "A").end(xlup).row
>> >
>> > This will give you the last row in column A with anything in it...
>> > --
>> > HTH...
>> >
>> > Jim Thomlinson
>> >
>> >
>> > "[email protected]" wrote:
>> >
>> >> Hi all,
>> >> Now I have a excel file, in sheet1,
>> >> the actual rows number is 20, that is, only the first 20 rows are
>> >> using.
>> >> But I use Activesheet.usedrange.rows.count, it gets 26, and sometimes
>> >> more.
>> >> But there's no content in those rows.
>> >> Why?
>> >> Thanks.
>> >>
>> >> michael
>> >>
>> >>
>>
>>
>>