Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Changing a Number in a Column Using Arrays | Excel Worksheet Functions | |||
Reference Number | Excel Discussion (Misc queries) | |||
chart label reference based on the column number | Charts and Charting in Excel | |||
chart label reference based on the column number | Charts and Charting in Excel | |||
return the column reference number of a function result | Excel Discussion (Misc queries) |