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

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

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

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

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

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

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