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

[MEUserII]

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?

 

[Scott Huish]

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?