Hello,

I have one workbook open, it has only one sheet in it....

Will it be possible to do this way...by code...or is there an existing one?

I want to divide the one-sheet into multiple groups of rows...

e.g. I place the criteria below <number of rows i need
<number of rows in grouped with distinct behaviour

A1 = 40
A2=1000
A3=2000
A4=25000
A5=(65536-A1-A2-A3-A4)

so if theres a macro,,,i run it and the result will re-arrange the behaviour
of this one sheet.
Meaning if i insert/delete cells or group of cells within the range A1:IV40,
the named_ranges from A41:IV65536 will not be changed dynamically.

Also the running state of the one sheet is that, it is logically behaving as
if they are divided into 5 separate sheets...where in fact they are just a
division of one 2003sheet such as these ranges...
A1:IV40
A41:IV1040
A1041:IV3040
A3041:IV28040
A28041:IV65536....
Regarding changes thru column *insert/delete* --Disabled
There must be no named_ranges that will over_lap with different group...

I can consider this to be the smartest macro i can find <to-date if there
is an existing one, for my end-goal for a stand-alone sheet <not Book...

thanks_and_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

Creating named ranges is not a problem BUT (if I understand correctly) you
delete cells (not rows/columns) from range 1 (1-40) then data from range 2
will "move" up to replace the deleted cells. I can't see how you stop this.

You do seem to want to make life complicated!!

"driller" wrote:

Hello,

I have one workbook open, it has only one sheet in it....

Will it be possible to do this way...by code...or is there an existing one?

I want to divide the one-sheet into multiple groups of rows...

e.g. I place the criteria below <number of rows i need
<number of rows in grouped with distinct behaviour

A1 = 40
A2=1000
A3=2000
A4=25000
A5=(65536-A1-A2-A3-A4)

so if theres a macro,,,i run it and the result will re-arrange the behaviour
of this one sheet.
Meaning if i insert/delete cells or group of cells within the range A1:IV40,
the named_ranges from A41:IV65536 will not be changed dynamically.

Also the running state of the one sheet is that, it is logically behaving as
if they are divided into 5 separate sheets...where in fact they are just a
division of one 2003sheet such as these ranges...
A1:IV40
A41:IV1040
A1041:IV3040
A3041:IV28040
A28041:IV65536....
Regarding changes thru column *insert/delete* --Disabled
There must be no named_ranges that will over_lap with different group...

I can consider this to be the smartest macro i can find <to-date if there
is an existing one, for my end-goal for a stand-alone sheet <not Book...

thanks_and_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 Sir Toppers,

Creating named ranges is not a problem BUT (if I understand correctly) you
delete cells (not rows/columns) from range 1 (1-40) then data from range 2
will "move" up to replace the deleted cells. I can't see how you stop this.

You do seem to want to make life complicated!!

it seems complicated for big file users...but for user like me who's
concentrated on many small yet linked tabulations separated in many sheets
and books,...

For me personally, if i can find that macro, it will be very helpful for a
one_user x one_sheet....in a small office.

looking forward for more ideas and advices..

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



"Toppers" wrote:

Creating named ranges is not a problem BUT (if I understand correctly) you
delete cells (not rows/columns) from range 1 (1-40) then data from range 2
will "move" up to replace the deleted cells. I can't see how you stop this.

You do seem to want to make life complicated!!

"driller" wrote:

Hello,

I have one workbook open, it has only one sheet in it....

Will it be possible to do this way...by code...or is there an existing one?

I want to divide the one-sheet into multiple groups of rows...

e.g. I place the criteria below <number of rows i need
<number of rows in grouped with distinct behaviour

A1 = 40
A2=1000
A3=2000
A4=25000
A5=(65536-A1-A2-A3-A4)

so if theres a macro,,,i run it and the result will re-arrange the behaviour
of this one sheet.
Meaning if i insert/delete cells or group of cells within the range A1:IV40,
the named_ranges from A41:IV65536 will not be changed dynamically.

Also the running state of the one sheet is that, it is logically behaving as
if they are divided into 5 separate sheets...where in fact they are just a
division of one 2003sheet such as these ranges...
A1:IV40
A41:IV1040
A1041:IV3040
A3041:IV28040
A28041:IV65536....
Regarding changes thru column *insert/delete* --Disabled
There must be no named_ranges that will over_lap with different group...

I can consider this to be the smartest macro i can find <to-date if there
is an existing one, for my end-goal for a stand-alone sheet <not Book...

thanks_and_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

I would like to know much more about what you are doing before adopting what
(to me) seems an extreme and error prone solution.

Perhaps the fundamental design of your "systems" is wrong.

"driller" wrote:

Hi Sir Toppers,

Creating named ranges is not a problem BUT (if I understand correctly) you
delete cells (not rows/columns) from range 1 (1-40) then data from range 2
will "move" up to replace the deleted cells. I can't see how you stop this.

You do seem to want to make life complicated!!

it seems complicated for big file users...but for user like me who's
concentrated on many small yet linked tabulations separated in many sheets
and books,...

For me personally, if i can find that macro, it will be very helpful for a
one_user x one_sheet....in a small office.

looking forward for more ideas and advices..

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



"Toppers" wrote:

Creating named ranges is not a problem BUT (if I understand correctly) you
delete cells (not rows/columns) from range 1 (1-40) then data from range 2
will "move" up to replace the deleted cells. I can't see how you stop this.

You do seem to want to make life complicated!!

"driller" wrote:

Hello,

I have one workbook open, it has only one sheet in it....

Will it be possible to do this way...by code...or is there an existing one?

I want to divide the one-sheet into multiple groups of rows...

e.g. I place the criteria below <number of rows i need
<number of rows in grouped with distinct behaviour

A1 = 40
A2=1000
A3=2000
A4=25000
A5=(65536-A1-A2-A3-A4)

so if theres a macro,,,i run it and the result will re-arrange the behaviour
of this one sheet.
Meaning if i insert/delete cells or group of cells within the range A1:IV40,
the named_ranges from A41:IV65536 will not be changed dynamically.

Also the running state of the one sheet is that, it is logically behaving as
if they are divided into 5 separate sheets...where in fact they are just a
division of one 2003sheet such as these ranges...
A1:IV40
A41:IV1040
A1041:IV3040
A3041:IV28040
A28041:IV65536....
Regarding changes thru column *insert/delete* --Disabled
There must be no named_ranges that will over_lap with different group...

I can consider this to be the smartest macro i can find <to-date if there
is an existing one, for my end-goal for a stand-alone sheet <not Book...

thanks_and_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 Toppers,

I know i can rely on you...

(i.e.)

In the first group (e.g.A1:IV40), I will place all the
constants,properties,parameters,general_criterias, whatever data...

In the Second Group (e.g A41:IV1040), I will place all the first processing
formulation....

In the Third Group (e.g A1041:IV3040), I will place all the iter-processing
formulation....

In the Fourth Group (e.g A3041:IV28040), I will place all the different
processing results....

In the Fifth Group (e.g. A28041:IV65536), I will place all the *named views*
for Print_Results and Reports...

Lastly, I will open 5 window for this one sheet, one window per group for
easy viewing.
Note: I can maximize the Auditing tool bars functionality...with one
summary_result *named_view*.


-So
assuming, after the macro was set-up <restricted behaviour of group of rows.
I will place a temporary sheet (e.g. Sheet2) which will act as a dummy
sheet. <One at a time. This dummy sheet are the sheets that come from all
linked books which are all open during the moment that I cut/paste the
named_tables located in the dummy Sheet....In short, there are somekind of
acceptable excel exercises that I will perform in order that these
old_named_ranges will be located in the One-Sheet..

Meaning also that the groups of rows are already pre-determined, hence the
range sizes is already anticipated...

The Problem I have is highlighted during the time that I will cut/paste the
old_named ranges...
During the name_transferring process, I will strictly monitor the ongoing
dynamic result_count of all the open workbooks <once every cut/paste
transfer......

Its complicated during the transfer, but the maintenance will be easy for me
as soon as all are transferred in one sheet...

The reason why i need that each group must behave independently with respect
to cut/insert/delete of cells is for a *Future purpose *that if there will be
a need to re-extend the range_tables* <Revised/updated references/Vers...not
so much big difference but it means it will grow*.
Otherwise,I need to run the macro again and another cut/paste operation will
need to be re-done....

I am planning to use the one-sheet in 2007Ver. yet i prefer to do it now in
2003 and just to saveas* 2007ver. later..

<If Possible only?

thanks_and_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



"Toppers" wrote:

I would like to know much more about what you are doing before adopting what
(to me) seems an extreme and error prone solution.

Perhaps the fundamental design of your "systems" is wrong.

"driller" wrote:

Hi Sir Toppers,

Creating named ranges is not a problem BUT (if I understand correctly) you
delete cells (not rows/columns) from range 1 (1-40) then data from range 2
will "move" up to replace the deleted cells. I can't see how you stop this.

You do seem to want to make life complicated!!

it seems complicated for big file users...but for user like me who's
concentrated on many small yet linked tabulations separated in many sheets
and books,...

For me personally, if i can find that macro, it will be very helpful for a
one_user x one_sheet....in a small office.

looking forward for more ideas and advices..

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



"Toppers" wrote:

Creating named ranges is not a problem BUT (if I understand correctly) you
delete cells (not rows/columns) from range 1 (1-40) then data from range 2
will "move" up to replace the deleted cells. I can't see how you stop this.

You do seem to want to make life complicated!!

"driller" wrote:

Hello,

I have one workbook open, it has only one sheet in it....

Will it be possible to do this way...by code...or is there an existing one?

I want to divide the one-sheet into multiple groups of rows...

e.g. I place the criteria below <number of rows i need
<number of rows in grouped with distinct behaviour

A1 = 40
A2=1000
A3=2000
A4=25000
A5=(65536-A1-A2-A3-A4)

so if theres a macro,,,i run it and the result will re-arrange the behaviour
of this one sheet.
Meaning if i insert/delete cells or group of cells within the range A1:IV40,
the named_ranges from A41:IV65536 will not be changed dynamically.

Also the running state of the one sheet is that, it is logically behaving as
if they are divided into 5 separate sheets...where in fact they are just a
division of one 2003sheet such as these ranges...
A1:IV40
A41:IV1040
A1041:IV3040
A3041:IV28040
A28041:IV65536....
Regarding changes thru column *insert/delete* --Disabled
There must be no named_ranges that will over_lap with different group...

I can consider this to be the smartest macro i can find <to-date if there
is an existing one, for my end-goal for a stand-alone sheet <not Book...

thanks_and_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

Despite your explanation (for which I would need to see w/book(s) to fully
understand and this is obviously not practical), I remain to be convinced
this is any "better" than having separate sheets, with named ranges etc as
required.

Although I don't use Excel "professionally" as I am retired (sort of!), I
would design applications with, for example, a separate sheet with
parameters, tables; equally, I would have a report sheet(s).

If you ever need to redefine a "group" range, the whole system will be at
risk. Using separate sheets, this risk is minimised if not eliminated.


"driller" wrote:

thanks Sir Toppers,

I know i can rely on you...

(i.e.)

In the first group (e.g.A1:IV40), I will place all the
constants,properties,parameters,general_criterias, whatever data...

In the Second Group (e.g A41:IV1040), I will place all the first processing
formulation....

In the Third Group (e.g A1041:IV3040), I will place all the iter-processing
formulation....

In the Fourth Group (e.g A3041:IV28040), I will place all the different
processing results....

In the Fifth Group (e.g. A28041:IV65536), I will place all the *named views*
for Print_Results and Reports...

Lastly, I will open 5 window for this one sheet, one window per group for
easy viewing.
Note: I can maximize the Auditing tool bars functionality...with one
summary_result *named_view*.


-So
assuming, after the macro was set-up <restricted behaviour of group of rows.
I will place a temporary sheet (e.g. Sheet2) which will act as a dummy
sheet. <One at a time. This dummy sheet are the sheets that come from all
linked books which are all open during the moment that I cut/paste the
named_tables located in the dummy Sheet....In short, there are somekind of
acceptable excel exercises that I will perform in order that these
old_named_ranges will be located in the One-Sheet..

Meaning also that the groups of rows are already pre-determined, hence the
range sizes is already anticipated...

The Problem I have is highlighted during the time that I will cut/paste the
old_named ranges...
During the name_transferring process, I will strictly monitor the ongoing
dynamic result_count of all the open workbooks <once every cut/paste
transfer......

Its complicated during the transfer, but the maintenance will be easy for me
as soon as all are transferred in one sheet...

The reason why i need that each group must behave independently with respect
to cut/insert/delete of cells is for a *Future purpose *that if there will be
a need to re-extend the range_tables* <Revised/updated references/Vers...not
so much big difference but it means it will grow*.
Otherwise,I need to run the macro again and another cut/paste operation will
need to be re-done....

I am planning to use the one-sheet in 2007Ver. yet i prefer to do it now in
2003 and just to saveas* 2007ver. later..

<If Possible only?

thanks_and_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



"Toppers" wrote:

I would like to know much more about what you are doing before adopting what
(to me) seems an extreme and error prone solution.

Perhaps the fundamental design of your "systems" is wrong.

"driller" wrote:

Hi Sir Toppers,

Creating named ranges is not a problem BUT (if I understand correctly) you
delete cells (not rows/columns) from range 1 (1-40) then data from range 2
will "move" up to replace the deleted cells. I can't see how you stop this.

You do seem to want to make life complicated!!

it seems complicated for big file users...but for user like me who's
concentrated on many small yet linked tabulations separated in many sheets
and books,...

For me personally, if i can find that macro, it will be very helpful for a
one_user x one_sheet....in a small office.

looking forward for more ideas and advices..

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



"Toppers" wrote:

Creating named ranges is not a problem BUT (if I understand correctly) you
delete cells (not rows/columns) from range 1 (1-40) then data from range 2
will "move" up to replace the deleted cells. I can't see how you stop this.

You do seem to want to make life complicated!!

"driller" wrote:

Hello,

I have one workbook open, it has only one sheet in it....

Will it be possible to do this way...by code...or is there an existing one?

I want to divide the one-sheet into multiple groups of rows...

e.g. I place the criteria below <number of rows i need
<number of rows in grouped with distinct behaviour

A1 = 40
A2=1000
A3=2000
A4=25000
A5=(65536-A1-A2-A3-A4)

so if theres a macro,,,i run it and the result will re-arrange the behaviour
of this one sheet.
Meaning if i insert/delete cells or group of cells within the range A1:IV40,
the named_ranges from A41:IV65536 will not be changed dynamically.

Also the running state of the one sheet is that, it is logically behaving as
if they are divided into 5 separate sheets...where in fact they are just a
division of one 2003sheet such as these ranges...
A1:IV40
A41:IV1040
A1041:IV3040
A3041:IV28040
A28041:IV65536....
Regarding changes thru column *insert/delete* --Disabled
There must be no named_ranges that will over_lap with different group...

I can consider this to be the smartest macro i can find <to-date if there
is an existing one, for my end-goal for a stand-alone sheet <not Book...

thanks_and_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