Hello Excel Experts,
I have 2 formulas here that find the last row and last column without VBA.
I tried to put them together, so that I'm able to have the last row and column on a named range. Would experts please assist me.
Thank you!
Last Row without VBA
Last Column without VBA
Please Login or Register to view this content.
Please Login or Register to view this content.
PerhapsPlease Login or Register to view this content.
Those will only work if you do not have any blank cells in row 3 or in column A.
What named range are you trying to define - from A3 to the last-used column and row ?
Pete
Thanks Pepe!
It works!!
If I may ask, why $1:$65535 ???
Is there a way to not make $1:$65535?
Reason being, it's limited to 65535...if the row has more than 65535, I would need to go back and change the row again.
That's why I have Sheet1!A:A,COUNTIF(Sheet1!A:A,"<>") so that it'll always "get" the last row of data
Hey Pete,
Yes, you're correct...in my case, row 3 and column A will never have blanks. The only time that it have blanks is that it is the last column and/or row.
The formula was made for XL2003. You can adapt that range as needed
So I guess there's no alternative of not having $1:$65535 in my named range then?
$1:$100000 will also do, you can make that part as long as you please
Another solution:
Please Login or Register to view this content.
Quang PT
I guess have no choice then...cause I really don't like to have it fixed to $100000...I would like excel to "automatically" know the row. Anyway, mark this as solved and rep increase!
Thanks Pepe!
INDIRECT being volatile might slow things really down..
In fact that row number delimits a region where INDEX gets it's range. So, again, INDEX in my suggestion returns a reference to the cell in the last row and columnOriginally Posted by dluhut
It is a common mistake to think that INDEX returns a value. It only does when not imbedded in a formula. In that case it returns a reference.
If you have some time this paper will make things much easier to grasp
Sure. But it depends on either data size or OP's taste
I tend to disagree, at least partially. Why not give an answer that is as solid as possible in any case (provided of course there are no blanks in the ranges) ?Sure. But it depends on either data size or OP's taste
The INDIRECT/ADDRESS combination in your formula can be replaced with INDEX, so why make things difficult ?
Just give him alternative option: LOOKUP can works with range with blank cells.
In additional, he was insist on keeping away from either 65535 or 100000. What number of rows are enough?
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks