Hi

The following function from Ron de Bruin will provide an answer.

Use as
=LAST(3,A1:IS65536)

Function Last(choice As Integer, Rng As Range)
'Ron de Bruin, 20 Feb 2007
' 1 = last row
' 2 = last column
' 3 = last cell
Dim lrw As Long
Dim lcol As Integer

Select Case choice

Case 1:
On Error Resume Next
Last = Rng.Find(What:="*", _
After:=Rng.Cells(1), _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False).row
On Error GoTo 0

Case 2:
On Error Resume Next
Last = Rng.Find(What:="*", _
After:=Rng.Cells(1), _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByColumns, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Column
On Error GoTo 0

Case 3:
On Error Resume Next
lrw = Rng.Find(What:="*", _
After:=Rng.Cells(1), _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False).row
On Error GoTo 0

On Error Resume Next
lcol = Rng.Find(What:="*", _
After:=Rng.Cells(1), _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByColumns, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Column
On Error GoTo 0

On Error Resume Next
Last = Cells(lrw, lcol).Address(False, False)
If Err.Number 0 Then
Last = Rng.Cells(1).Address(False, False)
Err.Clear
End If
On Error GoTo 0

End Select
End Function


--
Regards

Roger Govier


"driller" wrote in message
...
Good Day to all,

I have to make a dynamic named range(e.g. "test1") covering cell A1
down/right to the last cell that is not empty in my sheet1.
Criteria is....
1)named range : indirect("A1:"&"Maxcolumnreach (IS:IS) and MAXRowreach
(65535) and not empty"

so in my case, i need to embed the formula in the
InsertNameDefineName
something like this....
Name: Test1
=indirect("A1:"&"Maxcolumnreach (IS:IS) and MAXRowreach (65535) and
not empty"

so if the last non-empty cell is located on IR6000, the "Test1" range
=
A1:IR6000...

i just dont know other function to work with this completely.

regards,
driller
--
*****
birds of the same feather flock together..birdwise, it''s more worth
knowing the edges rather than focusing in one line! Know the limits
and
remember the extents - dive with Jonathan Seagull