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

How to get actual rows number in Excel using VBA?
 

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


" 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

How to get actual rows number in Excel using VBA?
 

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


" 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

How to get actual rows number in Excel using VBA?
 

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


" 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

How to get actual rows number in Excel using VBA?
 

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


" 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