Excel 2007 Function Qt

  • Thread starter Thread starter zyzzyva
  • Start date Start date
Z

zyzzyva

Any clues for speeding things up to return the location/range of the last
filled cell in a column?

Function LastInRange(InputRange As Range)
Dim CellCount As Long
Dim i As Long
CellCount = InputRange.Count
For i = CellCount To 1 Step -1
If Not IsEmpty(InputRange(i)) And IsNumeric(InputRange(i)) Then
Set LastInRange = InputRange(i)
Exit Function
End If
Next i
LastInRange = ""
End Function
 
Function LastInColumn(a As Range)
LastInColumn = Range("a1").Cells(1048576, a.Column).End(xlUp).Address
End Function
 
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.
 
Back
Top