How to judge the exact range used in a worksheet? 1

[hoki]

I know that excel has the properties of "CurrentRegion" and "UsedRange". But I feel that both can't really detect the range used. For example, CurrentRegion could only confine the region that isolated by blank cells around it.
That means one worksheet could has several region.

Regarding to UsedRange, it is more likely the one we want. However, I found that sometimes it will wrongly judge the range that is not really used but maybe just because it is used before though you clear it up now. Even delete the columns or rows can't refresh its recognization.

Does anyone notice this problem too? Any good way to find out the really used range?

Thanks for the help
Hoki

 

[xlbo]

The "UsedRange" method updates in certain circumstances - like when a save is done.
The most reliable method I've found is
lastRow = range("A65536&quot.end(xlup).row
lastCol = range("IV1&quot.end(xltoleft).column

However, using the usedrange method does seem to work with this sub (stolen from a Post Dale made)

Sub Used_CR() 'counts number of columns & rows used
usedrows = ActiveSheet.UsedRange.Rows.Count
usedcols = ActiveSheet.UsedRange.Columns.Count
MsgBox usedrows & " Rows " & usedcols & " Columns"
End Sub

As you say, usedrange can be wrong but this code seems to reset it when it runs and is therefore accurate - I have tested this using various deleting / clearcontents scenarios and it has returned the correct information for all that I have tried.

HTH
Geoff

 

[hoki]

Dear Geoff,
Thanks a lot for your sharing. I believe that's what I've been looking for and I will have a try later but just want to show my appreciation to you first.
Best Regards,
Hoki

 

[Loomah]

I'm impressed by this! Who gets the star, Geoff or Dale???

Beware of using xlUp & xltoleft if the used range is a strange shape

 

[SkipVought]

Hoki,

Since Excel has multiple sheets, it is best NOT to have multiple non-contiguous ranges on a sheet. I understand that often, workbooks come to us from sources that are beyond our control. But if you can control the design and structure of a workbook, then impose sound design principles that will not build in ambiguity and error. Skip,
metzgsk@voughtaircraft.com

 

[hoki]

Dear Geoff,
It seems not work for me. Is that because I missed the correct method to use the source code?
I try to type some strings in several cells of a worksheet. And then try to fill in a string in a cell at row 65536.

When I clear the content of row 65536. I run below sub:

Sub Used_CR() 'counts number of columns & rows used

ActiveSheet.UsedRange.Select

usedrows = ActiveSheet.UsedRange.Rows.Count
usedcols = ActiveSheet.UsedRange.Columns.Count
MsgBox usedrows & " Rows " & usedcols & " Columns"

ActiveSheet.UsedRange.Select


End Sub

And no matter before the codes that Dale provided or after, the selected range will be extended to row 65536. Even Save this file again can't change the result. The only way is to delete row 65536 and the usedrange will be corrected.

However, when we got an excel worksheet from some user, it is hard to know where the cells have been used and we need to delete them before the usedrange could be correctly applied.

I agree with Skip's suggestion that we could reduce such problems by well formatting the worksheet, but such situations are often happened.

Does anyone think it is a bug of Excel?

Best Regards,
Hoki

 

[xlbo]

I think it's cos you use the activesheet.usederange.select at the start - with the usedrange selected, it can't be reset - I think it is reset in this case by the usedrange.rows.COUNT ie when the coun t function is performed, the usedrange gets reset to what it should be. If you've got it selected, it is used and therefore the usedrange is not reset.
Try the code without the 1st line:
Sub Used_CR() 'counts number of columns & rows used

usedrows = ActiveSheet.UsedRange.Rows.Count
usedcols = ActiveSheet.UsedRange.Columns.Count
MsgBox usedrows & " Rows " & usedcols & " Columns"

ActiveSheet.UsedRange.Select


End Sub
HTH
Geoff

 

[hoki]

Dear Geoff,
It still seems not work for me. In fact, I've tried this sub by press F8 (step by step). I've tried to run Dale's code only, the Msgbox shows "65536 rows 4 columns", and even after these codes, the selection of usedrange still contains cells in row 65536 where the content had been cleared. Do you have a different result by the same code and scenario?


Hoki

 

[xlbo]

Hi Hoki - I have to say that I've tried to emulate your problem but however I clear the contents of row 65536, Dale's code seems to work. How are you putting a text string in there (code or manual), and how are you clearing it ??
What version of excel are you using ??

Geoff

 

[hoki]

Dear Geoff,
>How are you putting a text string in there (code or manual)
I manually input a string in a cell at row 65536 (to emulate common user's behavior)

>and how are you clearing it ??
Again, I manually press "Delete" button while the ActiveCell is at that cell I just input the string (still a common user's behavior).

>What version of excel are you using ??
Excel 2000 (9.0.3821 SR-1)

Thanks!

Best Regards,
Hoki

 

[Loomah]

Hoki

Lazily wandering accross various web sites I stumbled accross this...

http://www.beyondtechnology.com/geeks012.shtml

Hope it works for you. It did for me!

 

[xlbo]

Have been conversing with Hoki over email and exchanging files - seems like the reason his code wouldn't work is due to the font he was using - a Chinese font that was applied to all cells (up to 65536) which was causing excel to think that all rows were in the used range - weirdly, it didn't do the same for columns. Anyway, the long and the short of it is if you use non standard fonts, the UsedRange in excel may be unreliable.
HTH
Geoff

 

[Loomah]

I'd be interested to know if the solution in the above site works in this case as it doesn't use UsedRange at all.
;-)

 

[xlbo]

Having cannibalised it - here's a version that works
Sub FindUsedRange()
Dim LastRow, LastCol

' Find the last real row
LastRow = ActiveSheet.Cells.Find(What:="*", _
SearchDirection:=xlPrevious, _
SearchOrder:=xlByRows).Row

' Find the last real column
LastCol = ActiveSheet.Cells.Find(What:="*", _
SearchDirection:=xlPrevious, _
SearchOrder:=xlByColumns).Column

ActiveSheet.Range(Cells(1, 1),Cells(LastRow,LastCol)).Select
End Sub

This works for me - however, it won't get yo the start of the range
, so, if there is nothing in col A, col A will still be selected - Hoki - you wanna try this ??
Geoff

 

[Loomah]

By changing the search direction to xlNext it's possible to get the first used cell and now combining everything should be possible to obtain the whole range

Out of interest, I posted this thread (thread707-255392) in relation to solving this problem but I actually don't understand what I created. Anyone any ideas????????

 

[hoki]

Dear all,
First of all, I would like to thanks for Geoff's kindly help verifying the real problem I encountered. So I give him a star.
It looks that UsedRange is not reliable, and this is echoed by the link that Loomah provided. And thank Loomah that just find the exact answer from other site, it really works and is reliable. Looks like we are too focusing on making the UsedRange workable to miss another easy way solving the problem. I would like to give Loomah a star too, but it sounds the system doesn't let me to do so. I guess I only have one vote for this topic.

Anyway, thanks all of you.

Best Regards,
Hoki