Last Numeric in a Column?

[Jeff]

Hi all. Could anyone help me make a vba function, that returns the
last numeric (positive or negative) cell in a column.
for example in range (F6:F19):

cell value
F6 5
F7 10
F8 100
F9 169
F10 -
F11 -
.. -
.. -
.. -
F19 -

F20 =LastNumInColumn (F6:F19) OR 169
Please Note : "-" is a hypen

Thanks in advance guys.

Jeff

 

[Guest]

Function LastNumInColumn(r As Range) As Variant
LastNumInColumn = ""
For Each rr In r
If Application.WorksheetFunction.IsNumber(rr.Value) Then LastNumInColumn
= rr.Value
Next
End Function

will work for column, rows, or nearly any range.

 

[RB Smissaert]

Something like this should work and is pretty quick:


Function LastCellWithNumber(rngRange As Range, _
Optional bRightToLeft As Boolean) As Range

Dim r As Long
Dim c As Long
Dim arr
Dim UB1 As Long
Dim UB2 As Long

arr = rngRange

If rngRange.Cells.Count = 1 Then
If IsNumeric(arr) Then
Set LastCellWithNumber = rngRange
End If
Exit Function
End If

UB1 = UBound(arr)
UB2 = UBound(arr, 2)

If bRightToLeft Then
For c = UB2 To 1 Step -1
For r = UB1 To 1 Step -1
If Not IsEmpty(arr(r, c)) Then
If IsNumeric(arr(r, c)) Then
Set LastCellWithNumber = _
Cells((r + rngRange.Cells(1).Row) - 1, _
(c + rngRange.Cells(1).Column) - 1)
Exit Function
End If
End If
Next r
Next c
Else
For r = UB1 To 1 Step -1
For c = UB2 To 1 Step -1
If Not IsEmpty(arr(r, c)) Then
If IsNumeric(arr(r, c)) Then
Set LastCellWithNumber = _
Cells((r + rngRange.Cells(1).Row) - 1, _
(c + rngRange.Cells(1).Column) - 1)
Exit Function
End If
End If
Next c
Next r
End If

End Function


Sub test()

Dim rngLastNumber As Range

Set rngLastNumber = LastCellWithNumber(Range(Cells(1), Cells(20, 1)),
False)

If Not rngLastNumber Is Nothing Then
MsgBox rngLastNumber.Address
End If

End Sub


Bear in mind that the function could return Nothing, so you will have to
handle this
if you are using this as a worksheet function.


RBS

 

[Guest]

There is no need to write code. Bob Phillips has collected some of the
cleverest methods posted in the news groups at his site:

http://xldynamic.com/source/xld.LastValue.html