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 |
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 |
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 |
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 |
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 |
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 |
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 |
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 |
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 |
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 |
All times are GMT +1. The time now is 06:32 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com