Calculating the range (distance) between two unempty cells

[Usercode]

I have a range of values spread across in rows from A to AA. some cells are empty and some are not. and the cells that are not empty have unordered values. Is there a way to calculate the range between the first cell and last cell which have values? I hope explanation is good. I can do this with extra tables/ cells etc. but would it be possible to do this with a tricky formula? please. thanks. I posted an example image. In the first I would like to know the range between cell Y and cell E. and in the second row, it is between cell x and cell B.

 

[AhoyNC]

Maybe:
This is an array formula and must be entered with CTRL-SHIFT-ENTER.

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L
1		1	5				7				4
2			4	6		8	9		0	8
3	2		5
4
5	Range
6	Row 1	10
7	Row 2	8
8	Row 3	3
Sheet

 

[shg]

So is the answer 22 for your example?

 

[Usercode]

Thanks a lot, AhoyNC !! This is awesome!!

 

[Usercode]

yes, the formula works. AhoyNC's formula does the job.

 

[AhoyNC]

Your welcome. Thanks for the feedback.
SHG - not sure I understand your question. This is to calculate the number of cells from the 1st value in a row to the last value in the row.

 

[tddai1999]

Your welcome. Thanks for the feedback.
SHG - not sure I understand your question. This is to calculate the number of cells from the 1st value in a row to the last value in the row.

Thanh you so much, U almost saved my day. My problem is that what if I wanted to know the distance from the 1st value to the 2nd, or 3rd or 4th. Not to the last.