Value from last non black cell in a column

  • Thread starter Thread starter sbharbour
  • Start date Start date
S

sbharbour

I'm trying to figure out how to get the value from the last non-blank
cell in a column

but can't seem to figure out a formula to type in my cell to get that
value...

Any ideas?

TIA
 
Found the answer:

I added a module with this code:

Function LASTINCOLUMN(rngInput As Range)
Dim WorkRange As Range
Dim i As Integer, CellCount As Integer
Application.Volatile
Set WorkRange = rngInput.Columns(1).EntireColumn
Set WorkRange = Intersect(WorkRange.Parent.UsedRange, WorkRange)
CellCount = WorkRange.Count
For i = CellCount To 1 Step -1
If Not IsEmpty(WorkRange(i)) Then
LASTINCOLUMN = WorkRange(i).Value
Exit Function
End If
Next i
End Function

Then used this formula

=lastincolumn(G5)
 
I'm trying to figure out how to get the value from the last non-blank
cell in a column

but can't seem to figure out a formula to type in my cell to get that
value...

Any ideas?
Click to expand...

*Array-enter* =INDEX(A1:A65535,MAX(ISNUMBER(A1:A65535)*ROW(A1:A65535)))

To array-enter a formula, after typing or pasting it, hold down <ctrl><shift>
while hitting <enter>.


--ron
 
Or a little shorter (and probably also faster if speed is an issue):

'-----
Function LASTINCOLUMN2(rngInput As Range)
Application.Volatile
LASTINCOLUMN2 = Cells(Rows.Count, rngInput.Column).End(xlUp).Value
End Function
'-----

Your function works OK, but I use a shortcut, .End(xlUp), instead of the loop.

HTH
Anders Silvén

'-----
 
Back
Top