This belongs in the other forum, zyzzyva; you accidentally posted this one in
Worksheet Functions. But there is a shorter way, yes. Let's see, you're
returning a cell, right? Try this:
Function LastInRange(InputRange As Range)
LastInRange = InputRange.Cells(InputRange.SpecialCells(xlLastCell).Row +
1, InputRange.Column).End(xlUp)
End Function
Or shorten it by using the With statement:
Function LastInRange(InputRange As Range)
With InputRange
LastInRange = .Cells(.SpecialCells(xlLastCell).Row + 1, .Column).End(xlUp)
End With
End Function
Ok, that's the confusing fancy way, throwing everything into one statement.
Here's how it works: Pretend you're doing it manually: You hit <Ctl-End> to
get to the bottom of the worksheet, that is, the last used area. Then you
move the cursor to the column you're interested in and one row BELOW the last
row. Then you hit <End><Up> to get to the last occupied cell in that column.
To simulate that in VBA, I'll break it down this way:
Function LastInRange(InputRange As Range)
Set wso = InputRange.Worksheet 'this is the worksheet we're in.
rz = InputRange.SpecialCells(xlLastCell).Row 'last used row in wksheet
ca = InputRange.Column 'first column in the specified range
Set BottomCell = wso.Cells(rz + 1, ca) 'the cell BELOW the last row
Set LastInRange = BottomCell.End(xlUp) '<End><Up> result
End Function
Now, one problem with this: You said last filled cell in a column, but your
program looks for the last NUMERIC filled cell. If you need to leave out
text cells, we can still improve on your program but it'll take longer than
the above. That solution starts with the last row in the specified column,
and then starts searching from there:
Function LastInRange(InputRange As Range)
Set wso = InputRange.Worksheet 'this is the worksheet we're in.
rz = InputRange.SpecialCells(xlLastCell).Row 'last used row in wksheet
ca = InputRange.Column 'first column in the specified range
For jr = rz to 1 Step -1
Set co = wso.Cells(jr, ca)
if Not IsEmpty(co) and IsNumeric(co) Then
Set LastInRange = co
Exit For
End If
Next jr
End Function
That returns Empty if there's no numeric value in that column, or the cell
containing the last numeric value if there is.
If this still runs too long - as it might if you have more than a hundred
rows or so - you can make it run VERY quickly by getting Excel to put the
whole column into an array and then searching the array instead.
