Convert Text String to Single Letter Range/Single Number Range?

MEUserII

Board Regular
Joined
Oct 27, 2017
Messages
59
Consider the following two formulas for calculating the last row in a column and last column in a row:

Formula for calculating last row in a column:
=LOOKUP((2), ((1)/((E:E)<>("") ) ), (ROW(E:E) ) )

Formula for calculating last column in a row:
=LOOKUP((2), ((1)/((5:5)<>("") ) ), (COLUMN(5:5) ) )

For the case of calculating the last row in a column I was able to solve this with the following formula:

=LOOKUP((2), ((1)/((INDIRECT((SUBSTITUTE( (SUBSTITUTE( (ADDRESS( (1), (5), (1) ) ), ("1"), ("") ) ), ("$"), ("") ) )&(":")&(SUBSTITUTE( (SUBSTITUTE( (ADDRESS( (1), (5), (1) ) ), ("1"), ("") ) ), ("$"), ("") ) ) ) )<>("") ) ), (ROW(INDIRECT((SUBSTITUTE( (SUBSTITUTE( (ADDRESS( (1), (5), (1) ) ), ("1"), ("") ) ), ("$"), ("") ) )&(":")&(SUBSTITUTE( (SUBSTITUTE( (ADDRESS( (1), (5), (1) ) ), ("1"), ("") ) ), ("$"), ("") ) ) ) ) ) )

Would there by a way to create an active range of: 5:5 (which would be color highlighted to show it's an active range), in order to put in to the formula for calculating the last row in a column?

I tried the following attempt, but it resulted in a #N/A error.

=LOOKUP((2), ((1)/((INDIRECT((RIGHT( (ADDRESS( (1), (COLUMN() ), (1) )), (2) ) )&(":")&(RIGHT( (ADDRESS( (1), (COLUMN() ), (1) )), (2) ) ) ) )<>("") ) ), (COLUMN(INDIRECT((RIGHT( (ADDRESS( (1), (COLUMN() ), (1) )), (2) ) )&(":")&(RIGHT( (ADDRESS( (1), (COLUMN() ), (1) )), (2) ) ) ) ) ) )

Would anyone know how to be able to do this for the creating of an active range of: 5:5 (which would be color highlighted to show it's an active range), in order to put in to the formula for calculating the last row in a column?
 
Last edited:

Scott Huish

MrExcel MVP
Joined
Mar 17, 2004
Messages
19,899
Office Version
365, 2010
Platform
Windows
The first thing I would suggest is to stop putting parentheses around things that don't need it. That is very hard to read.

What do you mean by active range exactly, that it's selected or what?

What are you trying to return with the formula exactly?
 

Forum statistics

Threads
1,085,537
Messages
5,384,299
Members
401,887
Latest member
Somesh

Some videos you may like

This Week's Hot Topics

Top