Determining worksheet data area

[MARK858]

The last code run with

cell A1 as part of the range if the data is all on the first row, even when A1 isn't empty

 

[CephasOz]

@MARK858 - I ran the code you suggested, but in an empty worksheet it went to debug with an error. The point of my code was to have something that produced a usable result in every circumstance.

 

[CephasOz]

@MARK858 - Also, start out with an empty worksheet, put an "X" in the following cells: A1, C5, A12, and run your code. Then use my code as in:

Sub zzzz()
    Debug.Print LastUsedCell(ActiveSheet).Address
End Sub

and tell me which result is best in terms of ensuring you haven't missed any outliers.

 

[MARK858]

@MARK858 - I ran the code you suggested, but in an empty worksheet it went to debug with an error. The point of my code was to have something that produced a usable result in every circumstance.

You also stated that Find doesn't find cell A1

doesn't return the starting cell A1 as part of the range if the data is all on the first row, even when A1 isn't empty

which it does if you use all it's parameters.

My response was due to your statement above and was in no way a method to determine the complete used range and definitely not the last used cell.

 

[CephasOz]

@MARK858 - Firstly, thank you for your time, expertise, and commitment to this community. I can't speak for everyone, but I can for myself; I have appreciated your insights in many posts.

I made the statement about not returning A1 in response to the code that @pbornemeier was kind enough to suggest. It wasn't a definitive statement about the deficiencies or otherwise of FIND itself. (It would be strange to denigrate FIND considering that I use it in my code.)

If I want to ensure that I have the cell that is in both the last row and the last column, I'm happy to use my code, because it will always produce that result. I wrote it because I wanted a bullet-proof method that I can both count on and also use easily. So that's its only purpose, and if it does that, I'm content.

I can't help but feel that we've been posting at cross-purposes. Would it be better to keep posts about the article, rather than the side issues that come from the discussion? If you can find a bug in the code, or a way to speed things up, or some other improvement that doesn't change its result, that would be good. We'd all gain.

 

[MARK858]

If I want to ensure that I have the cell that is in both the last row and the last column, I'm happy to use my code, because it will always produce that result. I wrote it because I wanted a bullet-proof method that I can both count on and also use easily. So that's its only purpose, and if it does that, I'm content.

I understand now that you are only interested in determining the last cell rather than determining the data area, sorry I misunderstood the title of the article