named range with criteria.

[Guest]

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 Insert>Name>Define>Name
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

 

[Roger Govier]

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

 

[Guest]

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

 

[Guest]

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]

Hi

No difference (well, not measurable anyway)

 

[Guest]

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

 

[Roger Govier]

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.

 

[Guest]

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