See this page
http://www.rondebruin.nl/last.htm

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Jay" wrote in message ...
I was looking for a function to do what Jim's does, but it didn't quite meet
my needs. I needed a function to find the last cell with data in specific
rows/columns. I've modified Jim's function and I'm posting the results for
the next person that searches for the same thing.

The formatting is broken, but it should fix itself if you past it into an
editor.

Enjoy,
Jay

Public Function LastCellInRow(wks As Worksheet, RowNumber As Long) As Range
'
================================================== ================================================== =
' Function: LastCellInRow
' Purpose: Search a specific row in a worksheet and find the last cell in
the row with data.
' Parameters: wks -- worksheet to check for data
' RowNumber -- the row to check for data
' Returns: A range representing the last cell in the row with data.
' Calls: none
'
' Origin: Microsoft Office Online - Office Discussion Groups:
microsoft.public.excel.programming
'
[http://www.microsoft.com/office/comm...-27322d0ea6e8]
'
' Author: Original code by: Jim Thomlinson Unknown
' Revised by: Jay 14-Mar-2007
' Last Revision: Jay 14-Mar-2007
'
================================================== ================================================== =
Dim lngLastColumn As Long ' last column

' starting from the last cell of the row, move leftward looking for a
cell with data
lngLastColumn = wks.Cells.Find(What:="*", _
After:=wks.Cells(RowNumber, Columns.Count), _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Column

' if no data was found in the row
If lngLastColumn = 0 Then
' set the column to 1
lngLastColumn = 1
End If

' set the return range of the cell
Set LastCellInRow = wks.Cells(RowNumber, lngLastColumn)

End Function ' == LastCellInRow ==

Public Function LastCellInColumn(wks As Worksheet, ColumnNumber As Long) As
Range
'
================================================== ================================================== =
' Function: LastCellInColumn
' Purpose: Search a specific column in a worksheet and find the last cell in
the column with data.
' Parameters: wks -- worksheet to check for data
' ColumnNumber -- the column to check for data
' Returns: A range representing the last cell in the column with data.
' Calls: none
'
' Origin: Microsoft Office Online - Office Discussion Groups:
microsoft.public.excel.programming
'
[http://www.microsoft.com/office/comm...-27322d0ea6e8]
'
' Author: Original code by: Jim Thomlinson Unknown
' Revised by: Jay 14-Mar-2007
' Last Revision: Jay 14-Mar-2007
'
================================================== ================================================== =
Dim lngLastRow As Long ' last row

' starting from the last cell of the column, move upward looking for a
cell with data
lngLastRow = wks.Cells.Find(What:="*", _
After:=wks.Cells(Rows.Count, ColumnNumber), _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByColumns, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Row

' if no data was found in the column
If lngLastRow = 0 Then
' set the row to 1
lngLastRow = 1
End If

' set the return range of the cell
Set LastCellInColumn = wks.Cells(lngLastRow, ColumnNumber)

End Function ' == LastCellInColumn ==


"Jim Thomlinson" wrote:

Here is a simple function that I use to get you the true last cell in every
instance. Guaranteed every time...

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
--
HTH...

Jim Thomlinson