Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old January 18th 06, 12:36 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Mar 2007
Posts: 325
Default Problem with USEDRANGE.ROWS.COUNT reporting one row too many

Good morning, all,

The last entry in my worksheet is in row 15 yet
activesheet.usedrange.rows.count reports 16 - or whatever the lowest entry
is in the worksheet+1
Another worksheet in the same workbook using the same code retruns the
correct value.

I tried deleting all the rows below the last entry, to eliminate the
possibility of spaces etc, but to no avail - does anyone have any ideas as to
what might be going wrong?

Thanks in advance

Pete



  #2   Report Post  
Old January 18th 06, 12:43 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 5,302
Default Problem with USEDRANGE.ROWS.COUNT reporting one row too many

Hi Peter,

See Debra Dalgleish's suggestions for resetting the used range:

http://www.contextures.com/xlfaqApp.html#Unused


---
Regards,
Norman


"Peter Rooney" wrote in message
...
Good morning, all,

The last entry in my worksheet is in row 15 yet
activesheet.usedrange.rows.count reports 16 - or whatever the lowest entry
is in the worksheet+1
Another worksheet in the same workbook using the same code retruns the
correct value.

I tried deleting all the rows below the last entry, to eliminate the
possibility of spaces etc, but to no avail - does anyone have any ideas as
to
what might be going wrong?

Thanks in advance

Pete




  #3   Report Post  
Old January 18th 06, 01:52 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Mar 2007
Posts: 325
Default Problem with USEDRANGE.ROWS.COUNT reporting one row too many

Norman,

I took a look at this, and tried the suggestions, but they didn't seem to
make any difference, so perhaps I have a corrupted worksheet.

I tried re-entering the worksheet and the range was correct up to a point,
then it started going wrong again.
I'll just keep at it, I guess, although thanks for the useful link.

regards

Pete



"Norman Jones" wrote:

Hi Peter,

See Debra Dalgleish's suggestions for resetting the used range:

http://www.contextures.com/xlfaqApp.html#Unused


---
Regards,
Norman


"Peter Rooney" wrote in message
...
Good morning, all,

The last entry in my worksheet is in row 15 yet
activesheet.usedrange.rows.count reports 16 - or whatever the lowest entry
is in the worksheet+1
Another worksheet in the same workbook using the same code retruns the
correct value.

I tried deleting all the rows below the last entry, to eliminate the
possibility of spaces etc, but to no avail - does anyone have any ideas as
to
what might be going wrong?

Thanks in advance

Pete





  #4   Report Post  
Old January 18th 06, 02:01 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Mar 2007
Posts: 325
Default Problem with USEDRANGE.ROWS.COUNT reporting one row too many

Norman,

Something else.

If row 16 has no bottom border, usedrange.rows.count reports row 16 as the
last row. If it DOES have a bottom border, usedrange.rows.count reports row
17 as the last row.

Now I'm really confused!

Pete
"Norman Jones" wrote:

Hi Peter,

See Debra Dalgleish's suggestions for resetting the used range:

http://www.contextures.com/xlfaqApp.html#Unused


---
Regards,
Norman


"Peter Rooney" wrote in message
...
Good morning, all,

The last entry in my worksheet is in row 15 yet
activesheet.usedrange.rows.count reports 16 - or whatever the lowest entry
is in the worksheet+1
Another worksheet in the same workbook using the same code retruns the
correct value.

I tried deleting all the rows below the last entry, to eliminate the
possibility of spaces etc, but to no avail - does anyone have any ideas as
to
what might be going wrong?

Thanks in advance

Pete





  #5   Report Post  
Old January 18th 06, 02:20 PM posted to microsoft.public.excel.programming
JNW JNW is offline
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 480
Default Problem with USEDRANGE.ROWS.COUNT reporting one row too many

Maybe it makes sense if we switch things around. If the top of Row 16 has a
border than it is counted...

Does the other worksheet have borders? If so, I don't know what's
happening. If not then that might be the issue.

Also you may try removing all borders and making sure that you are not
adding a border at the top of row 16 but instead add it at the bottom of 15.
This has caused me issues in the past.

If all else fails you can always do something like the following...
numrows = activesheet.usedrange.rows.count
numrows = numrows - 1

"Peter Rooney" wrote:

Norman,

Something else.

If row 16 has no bottom border, usedrange.rows.count reports row 16 as the
last row. If it DOES have a bottom border, usedrange.rows.count reports row
17 as the last row.

Now I'm really confused!

Pete
"Norman Jones" wrote:

Hi Peter,

See Debra Dalgleish's suggestions for resetting the used range:

http://www.contextures.com/xlfaqApp.html#Unused


---
Regards,
Norman


"Peter Rooney" wrote in message
...
Good morning, all,

The last entry in my worksheet is in row 15 yet
activesheet.usedrange.rows.count reports 16 - or whatever the lowest entry
is in the worksheet+1
Another worksheet in the same workbook using the same code retruns the
correct value.

I tried deleting all the rows below the last entry, to eliminate the
possibility of spaces etc, but to no avail - does anyone have any ideas as
to
what might be going wrong?

Thanks in advance

Pete







  #6   Report Post  
Old January 18th 06, 02:42 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Mar 2007
Posts: 325
Default Problem with USEDRANGE.ROWS.COUNT reporting one row too many

Hi, JNW,

Both the databases are basically the same - they both have a thick outline
around them. One shows the last used row as being the last data row, the
other shows the last used row as being the row below the last data row.
I'm having to fudge the code in the way you suggest, but I'm not too happy
doing it, as I don't know what's causing the problem.

In my problem database, if I add a bottom border to the bottom database row,
it shows up as a top border for the row below - yet I only have the problem
on one of the two databases - one reports correctly, the other doesn't.

Still, as long as it works and I look out for it the next time I develop a
database.

Thanks for your interest and advice.

Pete



"JNW" wrote:

Maybe it makes sense if we switch things around. If the top of Row 16 has a
border than it is counted...

Does the other worksheet have borders? If so, I don't know what's
happening. If not then that might be the issue.

Also you may try removing all borders and making sure that you are not
adding a border at the top of row 16 but instead add it at the bottom of 15.
This has caused me issues in the past.

If all else fails you can always do something like the following...
numrows = activesheet.usedrange.rows.count
numrows = numrows - 1

"Peter Rooney" wrote:

Norman,

Something else.

If row 16 has no bottom border, usedrange.rows.count reports row 16 as the
last row. If it DOES have a bottom border, usedrange.rows.count reports row
17 as the last row.

Now I'm really confused!

Pete
"Norman Jones" wrote:

Hi Peter,

See Debra Dalgleish's suggestions for resetting the used range:

http://www.contextures.com/xlfaqApp.html#Unused


---
Regards,
Norman


"Peter Rooney" wrote in message
...
Good morning, all,

The last entry in my worksheet is in row 15 yet
activesheet.usedrange.rows.count reports 16 - or whatever the lowest entry
is in the worksheet+1
Another worksheet in the same workbook using the same code retruns the
correct value.

I tried deleting all the rows below the last entry, to eliminate the
possibility of spaces etc, but to no avail - does anyone have any ideas as
to
what might be going wrong?

Thanks in advance

Pete





  #7   Report Post  
Old January 18th 06, 03:04 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 5,441
Default Problem with USEDRANGE.ROWS.COUNT reporting one row too many

Pete,

The better solution is to not count on usedrange. Try something like

Range("B2").CurrentRegion.Rows.Count

Where B2 is a cell that you know is in your contiguous datatable.

HTH,
Bernie
MS Excel MVP


"Peter Rooney" wrote in message
...
Hi, JNW,

Both the databases are basically the same - they both have a thick outline
around them. One shows the last used row as being the last data row, the
other shows the last used row as being the row below the last data row.
I'm having to fudge the code in the way you suggest, but I'm not too happy
doing it, as I don't know what's causing the problem.

In my problem database, if I add a bottom border to the bottom database row,
it shows up as a top border for the row below - yet I only have the problem
on one of the two databases - one reports correctly, the other doesn't.

Still, as long as it works and I look out for it the next time I develop a
database.

Thanks for your interest and advice.

Pete



"JNW" wrote:

Maybe it makes sense if we switch things around. If the top of Row 16 has a
border than it is counted...

Does the other worksheet have borders? If so, I don't know what's
happening. If not then that might be the issue.

Also you may try removing all borders and making sure that you are not
adding a border at the top of row 16 but instead add it at the bottom of 15.
This has caused me issues in the past.

If all else fails you can always do something like the following...
numrows = activesheet.usedrange.rows.count
numrows = numrows - 1

"Peter Rooney" wrote:

Norman,

Something else.

If row 16 has no bottom border, usedrange.rows.count reports row 16 as the
last row. If it DOES have a bottom border, usedrange.rows.count reports row
17 as the last row.

Now I'm really confused!

Pete
"Norman Jones" wrote:

Hi Peter,

See Debra Dalgleish's suggestions for resetting the used range:

http://www.contextures.com/xlfaqApp.html#Unused


---
Regards,
Norman


"Peter Rooney" wrote in message
...
Good morning, all,

The last entry in my worksheet is in row 15 yet
activesheet.usedrange.rows.count reports 16 - or whatever the lowest entry
is in the worksheet+1
Another worksheet in the same workbook using the same code retruns the
correct value.

I tried deleting all the rows below the last entry, to eliminate the
possibility of spaces etc, but to no avail - does anyone have any ideas as
to
what might be going wrong?

Thanks in advance

Pete







  #8   Report Post  
Old January 18th 06, 03:22 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Mar 2007
Posts: 325
Default Problem with USEDRANGE.ROWS.COUNT reporting one row too many

Bernie,
I used to swear by currentregion, but I have now built some event code in to
my database to prevent prople from deleting all the entries in a database
row, because if they did, currentregion wouldn't work. If your database goes
from rows 10 to 20, and you erase all the entries in, say, row 15,
currentregion will only report up to row 14. I've now isolated the problem to
when I run a row delete macro, and think that possibly a range name might not
be being updated correctly, although I'm not sure whether or not usedrange
would pick up something that had a range name applied to it, as against
simply cell contents or formatting.

I'll keep you posted, if you're interested.

Thanks for your advice.

Pete



"Bernie Deitrick" wrote:

Pete,

The better solution is to not count on usedrange. Try something like

Range("B2").CurrentRegion.Rows.Count

Where B2 is a cell that you know is in your contiguous datatable.

HTH,
Bernie
MS Excel MVP


"Peter Rooney" wrote in message
...
Hi, JNW,

Both the databases are basically the same - they both have a thick outline
around them. One shows the last used row as being the last data row, the
other shows the last used row as being the row below the last data row.
I'm having to fudge the code in the way you suggest, but I'm not too happy
doing it, as I don't know what's causing the problem.

In my problem database, if I add a bottom border to the bottom database row,
it shows up as a top border for the row below - yet I only have the problem
on one of the two databases - one reports correctly, the other doesn't.

Still, as long as it works and I look out for it the next time I develop a
database.

Thanks for your interest and advice.

Pete



"JNW" wrote:

Maybe it makes sense if we switch things around. If the top of Row 16 has a
border than it is counted...

Does the other worksheet have borders? If so, I don't know what's
happening. If not then that might be the issue.

Also you may try removing all borders and making sure that you are not
adding a border at the top of row 16 but instead add it at the bottom of 15.
This has caused me issues in the past.

If all else fails you can always do something like the following...
numrows = activesheet.usedrange.rows.count
numrows = numrows - 1

"Peter Rooney" wrote:

Norman,

Something else.

If row 16 has no bottom border, usedrange.rows.count reports row 16 as the
last row. If it DOES have a bottom border, usedrange.rows.count reports row
17 as the last row.

Now I'm really confused!

Pete
"Norman Jones" wrote:

Hi Peter,

See Debra Dalgleish's suggestions for resetting the used range:

http://www.contextures.com/xlfaqApp.html#Unused


---
Regards,
Norman


"Peter Rooney" wrote in message
...
Good morning, all,

The last entry in my worksheet is in row 15 yet
activesheet.usedrange.rows.count reports 16 - or whatever the lowest entry
is in the worksheet+1
Another worksheet in the same workbook using the same code retruns the
correct value.

I tried deleting all the rows below the last entry, to eliminate the
possibility of spaces etc, but to no avail - does anyone have any ideas as
to
what might be going wrong?

Thanks in advance

Pete








  #9   Report Post  
Old January 18th 06, 03:31 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 5,441
Default Problem with USEDRANGE.ROWS.COUNT reporting one row too many

Peter,

There are many ways to skin any particular cat in Excel:

Cells(Rows.Count,2).End(xlUp).Row

would find the row of the last filled cell in column B.

Or, in case the user can insert columns, use a named range to identify your key column:

Cells(Rows.Count,Range("KeyIdColumn").Column).End( xlUp).Row

HTH,
Bernie
MS Excel MVP


"Peter Rooney" wrote in message
...
Bernie,
I used to swear by currentregion, but I have now built some event code in to
my database to prevent prople from deleting all the entries in a database
row, because if they did, currentregion wouldn't work. If your database goes
from rows 10 to 20, and you erase all the entries in, say, row 15,
currentregion will only report up to row 14. I've now isolated the problem to
when I run a row delete macro, and think that possibly a range name might not
be being updated correctly, although I'm not sure whether or not usedrange
would pick up something that had a range name applied to it, as against
simply cell contents or formatting.

I'll keep you posted, if you're interested.

Thanks for your advice.

Pete



"Bernie Deitrick" wrote:

Pete,

The better solution is to not count on usedrange. Try something like

Range("B2").CurrentRegion.Rows.Count

Where B2 is a cell that you know is in your contiguous datatable.

HTH,
Bernie
MS Excel MVP


"Peter Rooney" wrote in message
...
Hi, JNW,

Both the databases are basically the same - they both have a thick outline
around them. One shows the last used row as being the last data row, the
other shows the last used row as being the row below the last data row.
I'm having to fudge the code in the way you suggest, but I'm not too happy
doing it, as I don't know what's causing the problem.

In my problem database, if I add a bottom border to the bottom database row,
it shows up as a top border for the row below - yet I only have the problem
on one of the two databases - one reports correctly, the other doesn't.

Still, as long as it works and I look out for it the next time I develop a
database.

Thanks for your interest and advice.

Pete



"JNW" wrote:

Maybe it makes sense if we switch things around. If the top of Row 16 has a
border than it is counted...

Does the other worksheet have borders? If so, I don't know what's
happening. If not then that might be the issue.

Also you may try removing all borders and making sure that you are not
adding a border at the top of row 16 but instead add it at the bottom of 15.
This has caused me issues in the past.

If all else fails you can always do something like the following...
numrows = activesheet.usedrange.rows.count
numrows = numrows - 1

"Peter Rooney" wrote:

Norman,

Something else.

If row 16 has no bottom border, usedrange.rows.count reports row 16 as the
last row. If it DOES have a bottom border, usedrange.rows.count reports row
17 as the last row.

Now I'm really confused!

Pete
"Norman Jones" wrote:

Hi Peter,

See Debra Dalgleish's suggestions for resetting the used range:

http://www.contextures.com/xlfaqApp.html#Unused


---
Regards,
Norman


"Peter Rooney" wrote in message
...
Good morning, all,

The last entry in my worksheet is in row 15 yet
activesheet.usedrange.rows.count reports 16 - or whatever the lowest entry
is in the worksheet+1
Another worksheet in the same workbook using the same code retruns the
correct value.

I tried deleting all the rows below the last entry, to eliminate the
possibility of spaces etc, but to no avail - does anyone have any ideas as
to
what might be going wrong?

Thanks in advance

Pete










  #10   Report Post  
Old January 18th 06, 03:51 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Mar 2007
Posts: 325
Default Problem with USEDRANGE.ROWS.COUNT reporting one row too many

Bernie,

The problem seems to occur when I just do [Control]+[End], too, let alone
any VBA involvement!
I was wondering if my problem was something to do with a range name that was
still referring to a deleted row, but it's getting late and I'm clutching at
straws, now..! :-)

Pete



"Bernie Deitrick" wrote:

Peter,

There are many ways to skin any particular cat in Excel:

Cells(Rows.Count,2).End(xlUp).Row

would find the row of the last filled cell in column B.

Or, in case the user can insert columns, use a named range to identify your key column:

Cells(Rows.Count,Range("KeyIdColumn").Column).End( xlUp).Row

HTH,
Bernie
MS Excel MVP


"Peter Rooney" wrote in message
...
Bernie,
I used to swear by currentregion, but I have now built some event code in to
my database to prevent prople from deleting all the entries in a database
row, because if they did, currentregion wouldn't work. If your database goes
from rows 10 to 20, and you erase all the entries in, say, row 15,
currentregion will only report up to row 14. I've now isolated the problem to
when I run a row delete macro, and think that possibly a range name might not
be being updated correctly, although I'm not sure whether or not usedrange
would pick up something that had a range name applied to it, as against
simply cell contents or formatting.

I'll keep you posted, if you're interested.

Thanks for your advice.

Pete



"Bernie Deitrick" wrote:

Pete,

The better solution is to not count on usedrange. Try something like

Range("B2").CurrentRegion.Rows.Count

Where B2 is a cell that you know is in your contiguous datatable.

HTH,
Bernie
MS Excel MVP


"Peter Rooney" wrote in message
...
Hi, JNW,

Both the databases are basically the same - they both have a thick outline
around them. One shows the last used row as being the last data row, the
other shows the last used row as being the row below the last data row.
I'm having to fudge the code in the way you suggest, but I'm not too happy
doing it, as I don't know what's causing the problem.

In my problem database, if I add a bottom border to the bottom database row,
it shows up as a top border for the row below - yet I only have the problem
on one of the two databases - one reports correctly, the other doesn't.

Still, as long as it works and I look out for it the next time I develop a
database.

Thanks for your interest and advice.

Pete



"JNW" wrote:

Maybe it makes sense if we switch things around. If the top of Row 16 has a
border than it is counted...

Does the other worksheet have borders? If so, I don't know what's
happening. If not then that might be the issue.

Also you may try removing all borders and making sure that you are not
adding a border at the top of row 16 but instead add it at the bottom of 15.
This has caused me issues in the past.

If all else fails you can always do something like the following...
numrows = activesheet.usedrange.rows.count
numrows = numrows - 1

"Peter Rooney" wrote:

Norman,

Something else.

If row 16 has no bottom border, usedrange.rows.count reports row 16 as the
last row. If it DOES have a bottom border, usedrange.rows.count reports row
17 as the last row.

Now I'm really confused!

Pete
"Norman Jones" wrote:

Hi Peter,

See Debra Dalgleish's suggestions for resetting the used range:

http://www.contextures.com/xlfaqApp.html#Unused


---
Regards,
Norman


"Peter Rooney" wrote in message
...
Good morning, all,

The last entry in my worksheet is in row 15 yet
activesheet.usedrange.rows.count reports 16 - or whatever the lowest entry
is in the worksheet+1
Another worksheet in the same workbook using the same code retruns the
correct value.

I tried deleting all the rows below the last entry, to eliminate the
possibility of spaces etc, but to no avail - does anyone have any ideas as
to
what might be going wrong?

Thanks in advance

Pete













Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Real Value of .UsedRange.Rows.Count dazman Excel Worksheet Functions 2 August 25th 05 03:24 PM
UsedRange Count for each column HotRod Excel Programming 7 August 5th 05 11:40 PM
Wrong result returned by UsedRange.Rows.Count j[_4_] Excel Programming 3 June 20th 05 09:03 PM
Problem with UsedRange.Rows.Count alainB[_15_] Excel Programming 4 April 29th 04 10:29 PM
UsedRange problem Kobayashi[_26_] Excel Programming 4 January 30th 04 05:17 PM


All times are GMT +1. The time now is 02:45 AM.

Powered by vBulletin® Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
Copyright 2004-2019 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017