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

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

=OFFSET(summary!$A2,,,COUNTA(summary!$A:$A),COUNTA (summary!$1:$1))

This works PROVIDING cells in Column A and row 1 have no embedded blanks.
Also assumes A is longest column.

"driller" wrote:

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

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

Hi

No difference (well, not measurable anyway)

--
Regards

Roger Govier


"driller" wrote in message
...
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

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

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

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