Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Last NonBlank In Ranger
In reference to my earlier post, I had another idea that may be simpler.
Is there a formula to identify the last nonblank cell in a range? Thanks, |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Last NonBlank In Ranger
Use this User Defined Function:
Function lastnb(r As Range) As String lastnb = "" For Each cell In r If cell.Value = "" Then Else lastnb = cell.Address End If Next End Function -- Gary''s Student - gsnu200805 "Brent E" wrote: In reference to my earlier post, I had another idea that may be simpler. Is there a formula to identify the last nonblank cell in a range? Thanks, |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Last NonBlank In Ranger
Some samples found with a Google search "Excel last non blank"
=ROW(OFFSET(A1,COUNTA(A:A)-1,0)) =index($a:$a,match(-9e+306,$a:$a,-1),1) (array forula - use ctrl+shift+enter) Function LASTINROW(rngInput As Range) As Variant Dim WorkRange As Range Dim i As Integer, CellCount As Integer Application.Volatile Set WorkRange = rngInput.Rows(1).EntireRow Set WorkRange = Intersect(WorkRange.Parent.UsedRange, WorkRange) CellCount = WorkRange.Count For i = CellCount To 1 Step -1 If Not IsEmpty(WorkRange(i)) Then LASTINROW = WorkRange(i).Value Exit Function End If Next i End Function =LOOKUP(255,A2:Z2) - for numeric value =LOOKUP(REPT("z",255),A2:Z2) - for text value For the last numerical value in a range, try... =LOOKUP(9.99999999999999E+307,Range) INDEX(1:1,MAX(INDEX(COLUMN(1:1)*(NOT(ISBLANK(1:1)) ),))) to get the last non-blank value in my current worksheet. best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "Brent E" wrote in message ... In reference to my earlier post, I had another idea that may be simpler. Is there a formula to identify the last nonblank cell in a range? Thanks, |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Last NonBlank In Ranger
Great. Thanks guys. I'll give those a try.
"Bernard Liengme" wrote: Some samples found with a Google search "Excel last non blank" =ROW(OFFSET(A1,COUNTA(A:A)-1,0)) =index($a:$a,match(-9e+306,$a:$a,-1),1) (array forula - use ctrl+shift+enter) Function LASTINROW(rngInput As Range) As Variant Dim WorkRange As Range Dim i As Integer, CellCount As Integer Application.Volatile Set WorkRange = rngInput.Rows(1).EntireRow Set WorkRange = Intersect(WorkRange.Parent.UsedRange, WorkRange) CellCount = WorkRange.Count For i = CellCount To 1 Step -1 If Not IsEmpty(WorkRange(i)) Then LASTINROW = WorkRange(i).Value Exit Function End If Next i End Function =LOOKUP(255,A2:Z2) - for numeric value =LOOKUP(REPT("z",255),A2:Z2) - for text value For the last numerical value in a range, try... =LOOKUP(9.99999999999999E+307,Range) INDEX(1:1,MAX(INDEX(COLUMN(1:1)*(NOT(ISBLANK(1:1)) ),))) to get the last non-blank value in my current worksheet. best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "Brent E" wrote in message ... In reference to my earlier post, I had another idea that may be simpler. Is there a formula to identify the last nonblank cell in a range? Thanks, |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Last NonBlank In Ranger
Non-array, non-volatile formula for contents of last, non-blank cell in a
range: =LOOKUP(2,1/(A1:A100<""),A1:A100) -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Brent E" wrote in message ... Great. Thanks guys. I'll give those a try. "Bernard Liengme" wrote: Some samples found with a Google search "Excel last non blank" =ROW(OFFSET(A1,COUNTA(A:A)-1,0)) =index($a:$a,match(-9e+306,$a:$a,-1),1) (array forula - use ctrl+shift+enter) Function LASTINROW(rngInput As Range) As Variant Dim WorkRange As Range Dim i As Integer, CellCount As Integer Application.Volatile Set WorkRange = rngInput.Rows(1).EntireRow Set WorkRange = Intersect(WorkRange.Parent.UsedRange, WorkRange) CellCount = WorkRange.Count For i = CellCount To 1 Step -1 If Not IsEmpty(WorkRange(i)) Then LASTINROW = WorkRange(i).Value Exit Function End If Next i End Function =LOOKUP(255,A2:Z2) - for numeric value =LOOKUP(REPT("z",255),A2:Z2) - for text value For the last numerical value in a range, try... =LOOKUP(9.99999999999999E+307,Range) INDEX(1:1,MAX(INDEX(COLUMN(1:1)*(NOT(ISBLANK(1:1)) ),))) to get the last non-blank value in my current worksheet. best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "Brent E" wrote in message ... In reference to my earlier post, I had another idea that may be simpler. Is there a formula to identify the last nonblank cell in a range? Thanks, |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how to return the value of the last nonblank cell in a row? | Excel Discussion (Misc queries) | |||
define nonblank in a formula | Excel Worksheet Functions | |||
How do I countif the criteria is a nonblank? | Excel Worksheet Functions | |||
Require a cell to be nonblank if another cell is nonblank | Excel Discussion (Misc queries) | |||
Adding NonBlank Cells | Excel Worksheet Functions |