Thanks Sir Roger,

This LAST() macro from Sir Ron de Bruin works well....and i have to continue
about countif with indirect function to complete the remaining search...
regards
--
*****
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



"Roger Govier" wrote:

Hi

For the function, it doesn't matter.
It finds the last used row at the time it is called, where ever that
might be.
Insertions or deletions are catered for.

--
Regards

Roger Govier


"driller" wrote in message
...
thanks Sir Roger and Sir Toppers,

i got a twist here,

"will it be possible" and "how to" that if any of the rows within the
range
are deleted then the max row range shall still remain at 65535. ?
something like =LAST(3,A1:IS"fix_at_65535")

the 65535 moves up when i try to make routine test like a excel users
are
fond of doing by manual or macro <deleting of rows. I need the
search range
identified as fix range.

Then the Last() macro result is okey and give the address of the last
intersection of column n row (not empty), when the sheet is not
allowed to
insert or delete rows).


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



"driller" wrote:

thanks Roger,
it do works, yet will the calculation time be faster if i just like
to have
the Case 3, written in the code?

please advice.
thank you for quick reply..
--
*****
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



"Roger Govier" wrote:

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