Last NonBlank In Ranger

[Brent E]

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,

 

[Gary''s Student]

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

 

[Bernard Liengme]

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

 

[Brent E]

Great. Thanks guys. I'll give those a try.

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

 

[RagDyer]

Non-array, non-volatile formula for contents of last, non-blank cell in a
range:

=LOOKUP(2,1/(A1:A100<>""),A1:A100)