I think I saw in this forum a while back a solution for finding the last used
cell in a worksheet in the form of a function that gets passed to it the name
of the worksheet (I think) and returns a range that is the last used cell in
that worksheet (this I'm sure). The trouble is I can't seem to be able to
find it anymore.

The solution looks very similar to Jim Thomlinson's response to post "Find
last used column" dated 4/6/2006 except the function returns a range as noted
above.

If you can tell me where to find it, or provide the solution itself, that
would be great.
Thanks.

Not too sure what I posted way back then but then again I can't remember what
I did last weekend... How about something like this...

Public Function LastCell(Optional ByVal wks As Worksheet) As Range
Dim lngLastRow As Long
Dim intLastColumn As Integer

If wks Is Nothing Then Set wks = ActiveSheet
On Error Resume Next
lngLastRow = wks.Cells.Find(What:="*", _
After:=wks.Range("A1"), _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Row
intLastColumn = wks.Cells.Find(What:="*", _
After:=wks.Range("A1"), _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByColumns, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Column
On Error GoTo 0
If lngLastRow = 0 Then
lngLastRow = 1
intLastColumn = 1
End If
Set LastCell = wks.Cells(lngLastRow, intLastColumn)
End Function

This returns the last used cell on a sheet. If no sheet is supplied then it
uses the active sheet. Try it something like this...

sub test
msgbox LastCell(Sheets("Sheet1")).Address
msgbox LastCell(Sheets("Sheet2")).Value
msgbox LastCell().Address
end sub

--
HTH...

Jim Thomlinson


"rockhammer" wrote:

I think I saw in this forum a while back a solution for finding the last used
cell in a worksheet in the form of a function that gets passed to it the name
of the worksheet (I think) and returns a range that is the last used cell in
that worksheet (this I'm sure). The trouble is I can't seem to be able to
find it anymore.

The solution looks very similar to Jim Thomlinson's response to post "Find
last used column" dated 4/6/2006 except the function returns a range as noted
above.

If you can tell me where to find it, or provide the solution itself, that
would be great.
Thanks.

Not bad.

I have trouble with "before lunch today"<g


Gord Dibben MS Excel MVP

On Mon, 1 Oct 2007 17:08:01 -0700, Jim Thomlinson
wrote:

Not too sure what I posted way back then but then again I can't remember what
I did last weekend

Hi Jim, thanks for the quick reply. That must be it. If fact, I just
dicovered by searching your name that you posted that on 9/1/2006.


"Jim Thomlinson" wrote:

Not too sure what I posted way back then but then again I can't remember what
I did last weekend... How about something like this...

Public Function LastCell(Optional ByVal wks As Worksheet) As Range
Dim lngLastRow As Long
Dim intLastColumn As Integer

If wks Is Nothing Then Set wks = ActiveSheet
On Error Resume Next
lngLastRow = wks.Cells.Find(What:="*", _
After:=wks.Range("A1"), _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Row
intLastColumn = wks.Cells.Find(What:="*", _
After:=wks.Range("A1"), _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByColumns, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Column
On Error GoTo 0
If lngLastRow = 0 Then
lngLastRow = 1
intLastColumn = 1
End If
Set LastCell = wks.Cells(lngLastRow, intLastColumn)
End Function

This returns the last used cell on a sheet. If no sheet is supplied then it
uses the active sheet. Try it something like this...

sub test
msgbox LastCell(Sheets("Sheet1")).Address
msgbox LastCell(Sheets("Sheet2")).Value
msgbox LastCell().Address
end sub

--
HTH...

Jim Thomlinson


"rockhammer" wrote:

I think I saw in this forum a while back a solution for finding the last used
cell in a worksheet in the form of a function that gets passed to it the name
of the worksheet (I think) and returns a range that is the last used cell in
that worksheet (this I'm sure). The trouble is I can't seem to be able to
find it anymore.

The solution looks very similar to Jim Thomlinson's response to post "Find
last used column" dated 4/6/2006 except the function returns a range as noted
above.

If you can tell me where to find it, or provide the solution itself, that
would be great.
Thanks.