Last used column

[Mitch]

I need to create a named range in a worksheet where I know the upper left
cell in the range but do not know the bottom right cell. It will be the last
filled cell in both the column and row. How can I do it?

[Jim Thomlinson]

Here is a function for you ...

Sub Test
LastCell().Select
End Sub

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


"Mitch" wrote:

> I need to create a named range in a worksheet where I know the upper left
> cell in the range but do not know the bottom right cell. It will be the last
> filled cell in both the column and row. How can I do it?