MARK858
MrExcel MVP
- Joined
- Nov 12, 2010
- Messages
- 16,257
- Office Version
- 365
- Platform
- Windows
- Mobile
The last code run with
-
If you would like to post, please check out the MrExcel Message Board FAQ and register here. If you forgot your password, you can reset your password.
Determining worksheet data area
- Thread starter CephasOz
- Start date
Excel Facts
Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December
CephasOz
Active Member
- Joined
- Feb 18, 2020
- Messages
- 283
- Office Version
- 365
- Platform
- Windows
@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:
and tell me which result is best in terms of ensuring you haven't missed any outliers.
VBA Code:
Sub zzzz()
Debug.Print LastUsedCell(ActiveSheet).Address
End SubMARK858
MrExcel MVP
- Joined
- Nov 12, 2010
- Messages
- 16,257
- Office Version
- 365
- Platform
- Windows
- Mobile
You also stated that Find doesn't find cell A1
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
Active Member
- Joined
- Feb 18, 2020
- Messages
- 283
- Office Version
- 365
- Platform
- Windows
@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.
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
MrExcel MVP
- Joined
- Nov 12, 2010
- Messages
- 16,257
- Office Version
- 365
- Platform
- Windows
- Mobile
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
