Problem with USEDRANGE.ROWS.COUNT reporting one row too many

[Peter Rooney]

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 Jones]

Hi Peter,

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

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


---
Regards,
Norman


"Peter Rooney" <[email protected]> wrote in message
news:[email protected]...
> 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
>
>

[Peter Rooney]

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" <[email protected]> wrote in message
> news:[email protected]...
> > 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
> >
> >
>
>
>

[Peter Rooney]

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" <[email protected]> wrote in message
> news:[email protected]...
> > 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
> >
> >
>
>
>

[JNW]

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" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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
> > >
> > >
> >
> >
> >

[Peter Rooney]

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" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > 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
> > > >
> > > >
> > >
> > >
> > >

[Bernie Deitrick]

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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
>> > > news:[email protected]...
>> > > > 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
>> > > >
>> > > >
>> > >
>> > >
>> > >

[Peter Rooney]

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" <[email protected]> wrote in message
> news:[email protected]...
> > 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" <[email protected]> wrote in message
> >> > > news:[email protected]...
> >> > > > 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
> >> > > >
> >> > > >
> >> > >
> >> > >
> >> > >
>
>
>

[Bernie Deitrick]

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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
>> news:[email protected]...
>> > 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" <[email protected]> wrote in message
>> >> > > news:[email protected]...
>> >> > > > 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
>> >> > > >
>> >> > > >
>> >> > >
>> >> > >
>> >> > >
>>
>>
>>

[Peter Rooney]

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" <[email protected]> wrote in message
> news:[email protected]...
> > 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" <[email protected]> wrote in message
> >> news:[email protected]...
> >> > 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" <[email protected]> wrote in message
> >> >> > > news:[email protected]...
> >> >> > > > 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
> >> >> > > >
> >> >> > > >
> >> >> > >
> >> >> > >
> >> >> > >
> >>
> >>
> >>
>
>
>