I have around 50,000 rows of data down each sheet.
These are made up of approx 4000 areas of data. (the areas of data are
from columns A:N and between 2 and 30 rows deep)
Seperately I have a cells contain formula covering combinations of
2-30 cells deep.
I want to afix these formula to the 4000 areas.
Thus, if the first 10 rows of the sheet constituted Area 1 I would
want to refer to the complimentary 10 row formula range and afix it in
the adjoining column to the Area 1 (columnO). If the next range, Area
2, was 6 rows deep I would want to search for the 6 row formula range
and afix that to Area 2 (columnO), and so on.
Manually, over 6 sheets, I would have to do around 24,000 copy/pastes
and this isn't practical.
Does anyone know how it can be set up to run automatically??

Any helpful suggestions/comments would be appreciated. I'm familiar
with Excel macros - if that's at all useful.
Thanks.
Colwyn.

I see nothing in your description that would designate a group of cells as
an "area"

Is there any method of determing which set of rows should be gathered
together into an area?


Gord Dibben MS Excel MVP

On Tue, 9 Sep 2008 07:33:17 -0700 (PDT), colwyn
wrote:

I have around 50,000 rows of data down each sheet.
These are made up of approx 4000 areas of data. (the areas of data are
from columns A:N and between 2 and 30 rows deep)
Seperately I have a cells contain formula covering combinations of
2-30 cells deep.
I want to afix these formula to the 4000 areas.
Thus, if the first 10 rows of the sheet constituted Area 1 I would
want to refer to the complimentary 10 row formula range and afix it in
the adjoining column to the Area 1 (columnO). If the next range, Area
2, was 6 rows deep I would want to search for the 6 row formula range
and afix that to Area 2 (columnO), and so on.
Manually, over 6 sheets, I would have to do around 24,000 copy/pastes
and this isn't practical.
Does anyone know how it can be set up to run automatically??

Any helpful suggestions/comments would be appreciated. I'm familiar
with Excel macros - if that's at all useful.
Thanks.
Colwyn.

On Sep 9, 5:41*pm, Gord Dibben <gorddibbATshawDOTca wrote:
I see nothing in your description that would designate a group of cells as
an "area"

Is there any method of determing which set of rows should be gathered
together into an area?

Gord Dibben *MS Excel MVP

On Tue, 9 Sep 2008 07:33:17 -0700 (PDT), colwyn

wrote:
I have around 50,000 rows of data down each sheet.
These are made up of approx 4000 areas of data. (the areas of data are
from columns A:N and between 2 and 30 rows deep)
Seperately I have a cells contain formula covering combinations of
2-30 cells deep.
I want to afix these formula to the 4000 areas.
Thus, if the first 10 rows of the sheet constituted Area 1 I would
want to refer to the complimentary 10 row formula range and afix it in
the adjoining column to the Area 1 (columnO). If the next range, Area
2, was 6 rows deep I would want to search for the 6 row formula range
and afix that to Area 2 (columnO), and so on.
Manually, over 6 sheets, I would have to do around 24,000 copy/pastes
and this isn't practical.
Does anyone know how it can be set up to run automatically??

Any helpful suggestions/comments would be appreciated. I'm familiar
with Excel macros - if that's at all useful.
Thanks.
Colwyn.


Thanks Don. Yes - down one column of area X there are numbers which
reflect the number of rows in that area or block of data. What is
needed is a means of looking at this number and then copying the
approriate number of rows of formula and pasting them in column O of
area X.
Thanks.
Colwyn.

On Sep 10, 10:13*am, colwyn wrote:
On Sep 9, 5:41*pm, Gord Dibben <gorddibbATshawDOTca wrote:



I see nothing in your description that would designate a group of cells as
an "area"

Is there any method of determing which set of rows should be gathered
together into an area?

Gord Dibben *MS Excel MVP

On Tue, 9 Sep 2008 07:33:17 -0700 (PDT), colwyn

wrote:
I have around 50,000 rows of data down each sheet.
These are made up of approx 4000 areas of data. (the areas of data are
from columns A:N and between 2 and 30 rows deep)
Seperately I have a cells contain formula covering combinations of
2-30 cells deep.
I want to afix these formula to the 4000 areas.
Thus, if the first 10 rows of the sheet constituted Area 1 I would
want to refer to the complimentary 10 row formula range and afix it in
the adjoining column to the Area 1 (columnO). If the next range, Area
2, was 6 rows deep I would want to search for the 6 row formula range
and afix that to Area 2 (columnO), and so on.
Manually, over 6 sheets, I would have to do around 24,000 copy/pastes
and this isn't practical.
Does anyone know how it can be set up to run automatically??

Any helpful suggestions/comments would be appreciated. I'm familiar
with Excel macros - if that's at all useful.
Thanks.
Colwyn.

Thanks Gord. Yes - down one column of area X there are numbers which
reflect the number of rows in that area or block of data. What is
needed is a means of looking at this number and then copying the
approriate number of rows of formula and pasting them in column O of
area X.
Thanks.
Colwyn.

On Sep 9, 5:41*pm, Gord Dibben <gorddibbATshawDOTca wrote:
I see nothing in your description that would designate a group of cells as
an "area"

Is there any method of determing which set of rows should be gathered
together into an area?

Gord Dibben *MS Excel MVP

On Tue, 9 Sep 2008 07:33:17 -0700 (PDT), colwyn

wrote:
I have around 50,000 rows of data down each sheet.
These are made up of approx 4000 areas of data. (the areas of data are
from columns A:N and between 2 and 30 rows deep)
Seperately I have a cells contain formula covering combinations of
2-30 cells deep.
I want to afix these formula to the 4000 areas.
Thus, if the first 10 rows of the sheet constituted Area 1 I would
want to refer to the complimentary 10 row formula range and afix it in
the adjoining column to the Area 1 (columnO). If the next range, Area
2, was 6 rows deep I would want to search for the 6 row formula range
and afix that to Area 2 (columnO), and so on.
Manually, over 6 sheets, I would have to do around 24,000 copy/pastes
and this isn't practical.
Does anyone know how it can be set up to run automatically??

Any helpful suggestions/comments would be appreciated. I'm familiar
with Excel macros - if that's at all useful.
Thanks.
Colwyn.

Thanks Gord. Yes - down one column of area X there are numbers which
reflect the number of rows in that area or block of data. What is
needed is a means of looking at this number and then copying the
approriate number of rows of formula and pasting them in column O of
area X.
Thanks.
Colwyn.

I still don't understand which cells you want copied to column O

Which column has the numbers and are the numbers continuous for each area?

You want to "afix these formulas" to the area. Which formulas would those
be and are they in one column or multiple columns?

What functions are used in the formulas doing and what cells do they refer
to?

Is this the same workbook you are discussing with Don G. about inserting
rows?

Send it to my email.........change the AT and DOT..........with a clear
description of your needs.


Gord

On Wed, 10 Sep 2008 08:42:47 -0700 (PDT), colwyn
wrote:

On Sep 9, 5:41*pm, Gord Dibben <gorddibbATshawDOTca wrote:
I see nothing in your description that would designate a group of cells as
an "area"

Is there any method of determing which set of rows should be gathered
together into an area?

Gord Dibben *MS Excel MVP

On Tue, 9 Sep 2008 07:33:17 -0700 (PDT), colwyn

wrote:
I have around 50,000 rows of data down each sheet.
These are made up of approx 4000 areas of data. (the areas of data are
from columns A:N and between 2 and 30 rows deep)
Seperately I have a cells contain formula covering combinations of
2-30 cells deep.
I want to afix these formula to the 4000 areas.
Thus, if the first 10 rows of the sheet constituted Area 1 I would
want to refer to the complimentary 10 row formula range and afix it in
the adjoining column to the Area 1 (columnO). If the next range, Area
2, was 6 rows deep I would want to search for the 6 row formula range
and afix that to Area 2 (columnO), and so on.
Manually, over 6 sheets, I would have to do around 24,000 copy/pastes
and this isn't practical.
Does anyone know how it can be set up to run automatically??

Any helpful suggestions/comments would be appreciated. I'm familiar
with Excel macros - if that's at all useful.
Thanks.
Colwyn.

Thanks Gord. Yes - down one column of area X there are numbers which
reflect the number of rows in that area or block of data. What is
needed is a means of looking at this number and then copying the
approriate number of rows of formula and pasting them in column O of
area X.
Thanks.
Colwyn.

On Sep 10, 9:28*pm, Gord Dibben <gorddibbATshawDOTca wrote:
I still don't understand which cells you want copied to column O

Which column has the numbers and are the numbers continuous for each area?

You want to "afix these formulas" to the area. *Which formulas would those
be and are they in one column or multiple columns?

What functions are used in the *formulas doing and what cells do they refer
to?

Is this the same workbook you are discussing with Don G. about inserting
rows?

Send it to my email.........change the AT and DOT..........with a clear
description of your needs.

Gord

On Wed, 10 Sep 2008 08:42:47 -0700 (PDT), colwyn

wrote:
On Sep 9, 5:41*pm, Gord Dibben <gorddibbATshawDOTca wrote:
I see nothing in your description that would designate a group of cells as
an "area"

Is there any method of determing which set of rows should be gathered
together into an area?

Gord Dibben *MS Excel MVP

On Tue, 9 Sep 2008 07:33:17 -0700 (PDT), colwyn

wrote:
I have around 50,000 rows of data down each sheet.
These are made up of approx 4000 areas of data. (the areas of data are
from columns A:N and between 2 and 30 rows deep)
Seperately I have a cells contain formula covering combinations of
2-30 cells deep.
I want to afix these formula to the 4000 areas.
Thus, if the first 10 rows of the sheet constituted Area 1 I would
want to refer to the complimentary 10 row formula range and afix it in
the adjoining column to the Area 1 (columnO). If the next range, Area
2, was 6 rows deep I would want to search for the 6 row formula range
and afix that to Area 2 (columnO), and so on.
Manually, over 6 sheets, I would have to do around 24,000 copy/pastes
and this isn't practical.
Does anyone know how it can be set up to run automatically??

Any helpful suggestions/comments would be appreciated. I'm familiar
with Excel macros - if that's at all useful.
Thanks.
Colwyn.

Thanks Gord. Yes - down one column of area X there are numbers which
reflect the number of rows in that area or block of data. What is
needed is a means of looking at this number and then copying the
approriate number of rows of formula and pasting them in column O of
area X.
Thanks.
Colwyn.



Gord, thanks for your help in this matter. I've sorted it out now -
much to my surprise !!

However, (oh dear!) ...there is perhaps something else you may be
able to help with. It's related to the above.

The spreadsheet contains a series of blocks of data and there are no
breaks between rows. I want to put a blank row between each block of
data. Any suggestions as to how I might go about this ??
It would be really helpful if I could achieve this end.
Thanks.
Colwyn.

Sub InsertRow_At_Change()
'Sandy Mann July 1st, 2007
Dim LastRow As Long
Dim X As Long
LastRow = Cells(Rows.Count, 1).End(xlUp).Row
Application.ScreenUpdating = False

For X = LastRow To 3 Step -1
If Cells(X, 1).Value < Cells(X - 1, 1).Value Then
If Cells(X, 1).Value < "" Then
If Cells(X - 1, 1).Value < "" Then
Cells(X, 1).entirerow.Insert Shift:=xlDown
End If
End If
End If
Next X
Application.ScreenUpdating = True
End Sub

Assumes you have like values in Column A which designate the break in
blocks,


Gord

On Wed, 10 Sep 2008 15:17:10 -0700 (PDT), colwyn
wrote:


The spreadsheet contains a series of blocks of data and there are no
breaks between rows. I want to put a blank row between each block of
data. Any suggestions as to how I might go about this ??
It would be really helpful if I could achieve this end.
Thanks.
Colwyn.