Reference of First or Last Number in a Row
 

Is there a formula that returns the first (or last) column that is populated
in a row?

Row 6 has A6=blank, B6=1, C6=5, D6=4, E6=blank

If looking for the first number, I would like it to return Column B, for the
last, Column D.

Thanks!

Rob.

Reference of First or Last Number in a Row
 

This UDF returns the column number of the first non-blank cell in row n

Function fnblank(n As Long) As Integer
fnblank = 0
For i = 1 To 256
If IsEmpty(Cells(n, i)) Then
Else
fnblank = i
Exit Function
End If
Next
End Function


This UDF returns the column number of the last non-blank cell in row n


Function lnblank(n As Long) As Integer
lnblank = 0
For i = 1 To 256
If IsEmpty(Cells(n, i)) Then
Else
lnblank = i
End If
Next
End Function
--
Gary's Student


"rob_bates" wrote:

Is there a formula that returns the first (or last) column that is populated
in a row?

Row 6 has A6=blank, B6=1, C6=5, D6=4, E6=blank

If looking for the first number, I would like it to return Column B, for the
last, Column D.

Thanks!

Rob.

Reference of First or Last Number in a Row
 

For i = 1 To 256

Now that Excel 2007 is out, you should use Columns.Count rather than hard
coding 256.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
(email address is on the web site)



"Gary''s Student" wrote in message
...
This UDF returns the column number of the first non-blank cell in row n

Function fnblank(n As Long) As Integer
fnblank = 0
For i = 1 To 256
If IsEmpty(Cells(n, i)) Then
Else
fnblank = i
Exit Function
End If
Next
End Function


This UDF returns the column number of the last non-blank cell in row n


Function lnblank(n As Long) As Integer
lnblank = 0
For i = 1 To 256
If IsEmpty(Cells(n, i)) Then
Else
lnblank = i
End If
Next
End Function
--
Gary's Student


"rob_bates" wrote:

Is there a formula that returns the first (or last) column that is
populated
in a row?

Row 6 has A6=blank, B6=1, C6=5, D6=4, E6=blank

If looking for the first number, I would like it to return Column B, for
the
last, Column D.

Thanks!

Rob.

Reference of First or Last Number in a Row
 

Thanks...I tried to formulate something with LOOKUP and MATCH, but had no luck.
--
Gary's Student


"Chip Pearson" wrote:

For i = 1 To 256

Now that Excel 2007 is out, you should use Columns.Count rather than hard
coding 256.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
(email address is on the web site)



"Gary''s Student" wrote in message
...
This UDF returns the column number of the first non-blank cell in row n

Function fnblank(n As Long) As Integer
fnblank = 0
For i = 1 To 256
If IsEmpty(Cells(n, i)) Then
Else
fnblank = i
Exit Function
End If
Next
End Function


This UDF returns the column number of the last non-blank cell in row n


Function lnblank(n As Long) As Integer
lnblank = 0
For i = 1 To 256
If IsEmpty(Cells(n, i)) Then
Else
lnblank = i
End If
Next
End Function
--
Gary's Student


"rob_bates" wrote:

Is there a formula that returns the first (or last) column that is
populated
in a row?

Row 6 has A6=blank, B6=1, C6=5, D6=4, E6=blank

If looking for the first number, I would like it to return Column B, for
the
last, Column D.

Thanks!

Rob.

Reference of First or Last Number in a Row
 

Some thoughts ..

Return First column (leftmost):
Put in say, A5, then array-enter (Press CTRL+SHIFT+ENTER):
=INDEX(6:6,MATCH(TRUE,6:6<"",0))

Return Last column (rightmost)
Put in say, B5, then array-enter (Press CTRL+SHIFT+ENTER):
=INDEX(6:6,,MAX((6:6<"")*(COLUMN(6:6))))
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"rob_bates" wrote:
Is there a formula that returns the first (or last) column that is populated
in a row?

Row 6 has A6=blank, B6=1, C6=5, D6=4, E6=blank

If looking for the first number, I would like it to return Column B, for the
last, Column D.

Thanks!

Rob.

Reference of First or Last Number in a Row
 

Returns the first column
=INDEX(A6:E6,MATCH(TRUE,6:6<"",0))

Rturns the last column
=LOOKUP(10^10,6:6,6:6)


"rob_bates" wrote:

Is there a formula that returns the first (or last) column that is populated
in a row?

Row 6 has A6=blank, B6=1, C6=5, D6=4, E6=blank

If looking for the first number, I would like it to return Column B, for the
last, Column D.

Thanks!

Rob.

Reference of First or Last Number in a Row
 

Thanks! This is close; it is finding the first and last column and
returning the amounts, I was trying to find which column number it is.

Any thoughts?

"Max" wrote:

Some thoughts ..

Return First column (leftmost):
Put in say, A5, then array-enter (Press CTRL+SHIFT+ENTER):
=INDEX(6:6,MATCH(TRUE,6:6<"",0))

Return Last column (rightmost)
Put in say, B5, then array-enter (Press CTRL+SHIFT+ENTER):
=INDEX(6:6,,MAX((6:6<"")*(COLUMN(6:6))))
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"rob_bates" wrote:
Is there a formula that returns the first (or last) column that is populated
in a row?

Row 6 has A6=blank, B6=1, C6=5, D6=4, E6=blank

If looking for the first number, I would like it to return Column B, for the
last, Column D.

Thanks!

Rob.

Reference of First or Last Number in a Row
 

Max:

I figured it out; I put reference numbers in row 5 and then changed it to:

=INDEX(5:5,MATCH(TRUE,6:6<"",0))

Thanks so much for your help; I learned something new!

"Max" wrote:

Some thoughts ..

Return First column (leftmost):
Put in say, A5, then array-enter (Press CTRL+SHIFT+ENTER):
=INDEX(6:6,MATCH(TRUE,6:6<"",0))

Return Last column (rightmost)
Put in say, B5, then array-enter (Press CTRL+SHIFT+ENTER):
=INDEX(6:6,,MAX((6:6<"")*(COLUMN(6:6))))
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"rob_bates" wrote:
Is there a formula that returns the first (or last) column that is populated
in a row?

Row 6 has A6=blank, B6=1, C6=5, D6=4, E6=blank

If looking for the first number, I would like it to return Column B, for the
last, Column D.

Thanks!

Rob.

Reference of First or Last Number in a Row
 

Glad you worked it out, Rob !

To return just the column numbers,
we could use the MATCH part of the expressions
viz, array-entered:
=MATCH(TRUE,6:6<"",0)
=MAX((6:6<"")*(COLUMN(6:6)))
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"rob_bates" wrote in message
...
Max:

I figured it out; I put reference numbers in row 5 and then changed it
to:

=INDEX(5:5,MATCH(TRUE,6:6<"",0))

Thanks so much for your help; I learned something new!