named range with criteria.
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 |
named range with criteria.
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 |
named range with criteria.
=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 |
named range with criteria.
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 |
named range with criteria.
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 |
named range with criteria.
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 |
named range with criteria.
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 |
named range with criteria.
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 |
All times are GMT +1. The time now is 12:27 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com