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.

Gord, very many thanks for your help
Your code only works when there are numbers in all cells.
The column I use it on is column A. Now if the first block of data is
6 rows deep, cell A1 contains 1. The next number to appear will be 2
in cell A7. Cells A2:A6 are blank.
Is there a workaround for this in your code ??
Thanks again.
Colwyn.



On Sep 11, 1:27*am, Gord Dibben <gorddibbATshawDOTca wrote:
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.

Run this macro

Sub Fill_Blanks()
'by Dave Peterson 2004-01-06
'fill blank cells in column with value above
Dim wks As Worksheet
Dim rng As Range
Dim LastRow As Long
Dim Col As Long

Set wks = ActiveSheet
With wks
Col = ActiveCell.Column
'or
'col = .range("b1").column

Set rng = .UsedRange 'try to reset the lastcell
LastRow = .Cells.SpecialCells(xlCellTypeLastCell).Row
Set rng = Nothing
On Error Resume Next
Set rng = .Range(.Cells(2, Col), .Cells(LastRow, Col)) _
.Cells.SpecialCells(xlCellTypeBlanks)
On Error GoTo 0

If rng Is Nothing Then
MsgBox "No blanks found"
Exit Sub
Else
rng.NumberFormat = "General"
rng.FormulaR1C1 = "=R[-1]C"
End If

'replace formulas with values
With .Cells(1, Col).EntireColumn
.Value = .Value
End With

End With

End Sub


Gord

On Thu, 11 Sep 2008 01:47:39 -0700 (PDT), colwyn
wrote:

Gord, very many thanks for your help
Your code only works when there are numbers in all cells.
The column I use it on is column A. Now if the first block of data is
6 rows deep, cell A1 contains 1. The next number to appear will be 2
in cell A7. Cells A2:A6 are blank.
Is there a workaround for this in your code ??
Thanks again.
Colwyn.



On Sep 11, 1:27*am, Gord Dibben <gorddibbATshawDOTca wrote:
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.

Thanks Gord Bet you'll be glad to hear that !!
Thanks for all.
Colwyn.



On Sep 11, 4:04*pm, Gord Dibben <gorddibbATshawDOTca wrote:
Run this macro

Sub Fill_Blanks()
'by Dave Peterson *2004-01-06
'fill blank cells in column with value above
Dim wks As Worksheet
Dim rng As Range
Dim LastRow As Long
Dim Col As Long

* * Set wks = ActiveSheet
* * With wks
* * * * Col = ActiveCell.Column
* * 'or
* * 'col = .range("b1").column

* * * * Set rng = .UsedRange * * * *'try to reset the lastcell
* * * * LastRow = .Cells.SpecialCells(xlCellTypeLastCell).Row
* * * * Set rng = Nothing
* * * * On Error Resume Next
* * * * Set rng = .Range(.Cells(2, Col), .Cells(LastRow, Col)) _
* * * * * * * * .Cells.SpecialCells(xlCellTypeBlanks)
* * * * On Error GoTo 0

* * * * If rng Is Nothing Then
* * * * * * MsgBox "No blanks found"
* * * * * * Exit Sub
* * * * Else
* * * * * * *rng.NumberFormat = "General"
* * * * * * rng.FormulaR1C1 = "=R[-1]C"
* * * * End If

* * 'replace formulas with values
* * * * With .Cells(1, Col).EntireColumn
* * * * * * .Value = .Value
* * * * End With

* * End With

End Sub

Gord

On Thu, 11 Sep 2008 01:47:39 -0700 (PDT), colwyn

wrote:
Gord, very many thanks for your help
Your code only works when there are numbers in all cells.
The column I use it on is column A. Now if the first block of data is
6 rows deep, cell A1 contains 1. The next number to appear will be 2
in cell A7. Cells A2:A6 are blank.
Is there a workaround for this in your code ??
Thanks again.
Colwyn.

On Sep 11, 1:27*am, Gord Dibben <gorddibbATshawDOTca wrote:
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.

Can you let Don know you are done with this?

You have two threads going with the same needs.


Gord

On Thu, 11 Sep 2008 08:34:54 -0700 (PDT), colwyn
wrote:

Thanks Gord Bet you'll be glad to hear that !!
Thanks for all.
Colwyn.



On Sep 11, 4:04*pm, Gord Dibben <gorddibbATshawDOTca wrote:
Run this macro

Sub Fill_Blanks()
'by Dave Peterson *2004-01-06
'fill blank cells in column with value above
Dim wks As Worksheet
Dim rng As Range
Dim LastRow As Long
Dim Col As Long

* * Set wks = ActiveSheet
* * With wks
* * * * Col = ActiveCell.Column
* * 'or
* * 'col = .range("b1").column

* * * * Set rng = .UsedRange * * * *'try to reset the lastcell
* * * * LastRow = .Cells.SpecialCells(xlCellTypeLastCell).Row
* * * * Set rng = Nothing
* * * * On Error Resume Next
* * * * Set rng = .Range(.Cells(2, Col), .Cells(LastRow, Col)) _
* * * * * * * * .Cells.SpecialCells(xlCellTypeBlanks)
* * * * On Error GoTo 0

* * * * If rng Is Nothing Then
* * * * * * MsgBox "No blanks found"
* * * * * * Exit Sub
* * * * Else
* * * * * * *rng.NumberFormat = "General"
* * * * * * rng.FormulaR1C1 = "=R[-1]C"
* * * * End If

* * 'replace formulas with values
* * * * With .Cells(1, Col).EntireColumn
* * * * * * .Value = .Value
* * * * End With

* * End With

End Sub

Gord

On Thu, 11 Sep 2008 01:47:39 -0700 (PDT), colwyn

wrote:
Gord, very many thanks for your help
Your code only works when there are numbers in all cells.
The column I use it on is column A. Now if the first block of data is
6 rows deep, cell A1 contains 1. The next number to appear will be 2
in cell A7. Cells A2:A6 are blank.
Is there a workaround for this in your code ??
Thanks again.
Colwyn.

On Sep 11, 1:27*am, Gord Dibben <gorddibbATshawDOTca wrote:
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.

Yes I did that yesterday Gord.
Colwyn.



On Sep 11, 5:21*pm, Gord Dibben <gorddibbATshawDOTca wrote:
Can you let Don know you are done with this?

You have two threads going with the same needs.

Gord

On Thu, 11 Sep 2008 08:34:54 -0700 (PDT), colwyn

wrote:
Thanks Gord Bet you'll be glad to hear that !!
Thanks for all.
Colwyn.

On Sep 11, 4:04*pm, Gord Dibben <gorddibbATshawDOTca wrote:
Run this macro

Sub Fill_Blanks()
'by Dave Peterson *2004-01-06
'fill blank cells in column with value above
Dim wks As Worksheet
Dim rng As Range
Dim LastRow As Long
Dim Col As Long

* * Set wks = ActiveSheet
* * With wks
* * * * Col = ActiveCell.Column
* * 'or
* * 'col = .range("b1").column

* * * * Set rng = .UsedRange * * * *'try to reset the lastcell
* * * * LastRow = .Cells.SpecialCells(xlCellTypeLastCell).Row
* * * * Set rng = Nothing
* * * * On Error Resume Next
* * * * Set rng = .Range(.Cells(2, Col), .Cells(LastRow, Col)) _
* * * * * * * * .Cells.SpecialCells(xlCellTypeBlanks)
* * * * On Error GoTo 0

* * * * If rng Is Nothing Then
* * * * * * MsgBox "No blanks found"
* * * * * * Exit Sub
* * * * Else
* * * * * * *rng.NumberFormat = "General"
* * * * * * rng.FormulaR1C1 = "=R[-1]C"
* * * * End If

* * 'replace formulas with values
* * * * With .Cells(1, Col).EntireColumn
* * * * * * .Value = .Value
* * * * End With

* * End With

End Sub

Gord

On Thu, 11 Sep 2008 01:47:39 -0700 (PDT), colwyn

wrote:
Gord, very many thanks for your help
Your code only works when there are numbers in all cells.
The column I use it on is column A. Now if the first block of data is
6 rows deep, cell A1 contains 1. The next number to appear will be 2
in cell A7. Cells A2:A6 are blank.
Is there a workaround for this in your code ??
Thanks again.
Colwyn.

On Sep 11, 1:27*am, Gord Dibben <gorddibbATshawDOTca wrote:
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.

I saw that.

Thanks

On Fri, 12 Sep 2008 08:50:00 -0700 (PDT), colwyn
wrote:

Yes I did that yesterday Gord.
Colwyn.



On Sep 11, 5:21*pm, Gord Dibben <gorddibbATshawDOTca wrote:
Can you let Don know you are done with this?

You have two threads going with the same needs.

Gord

On Thu, 11 Sep 2008 08:34:54 -0700 (PDT), colwyn

wrote:
Thanks Gord Bet you'll be glad to hear that !!
Thanks for all.
Colwyn.

On Sep 11, 4:04*pm, Gord Dibben <gorddibbATshawDOTca wrote:
Run this macro

Sub Fill_Blanks()
'by Dave Peterson *2004-01-06
'fill blank cells in column with value above
Dim wks As Worksheet
Dim rng As Range
Dim LastRow As Long
Dim Col As Long

* * Set wks = ActiveSheet
* * With wks
* * * * Col = ActiveCell.Column
* * 'or
* * 'col = .range("b1").column

* * * * Set rng = .UsedRange * * * *'try to reset the lastcell
* * * * LastRow = .Cells.SpecialCells(xlCellTypeLastCell).Row
* * * * Set rng = Nothing
* * * * On Error Resume Next
* * * * Set rng = .Range(.Cells(2, Col), .Cells(LastRow, Col)) _
* * * * * * * * .Cells.SpecialCells(xlCellTypeBlanks)
* * * * On Error GoTo 0

* * * * If rng Is Nothing Then
* * * * * * MsgBox "No blanks found"
* * * * * * Exit Sub
* * * * Else
* * * * * * *rng.NumberFormat = "General"
* * * * * * rng.FormulaR1C1 = "=R[-1]C"
* * * * End If

* * 'replace formulas with values
* * * * With .Cells(1, Col).EntireColumn
* * * * * * .Value = .Value
* * * * End With

* * End With

End Sub

Gord

On Thu, 11 Sep 2008 01:47:39 -0700 (PDT), colwyn

wrote:
Gord, very many thanks for your help
Your code only works when there are numbers in all cells.
The column I use it on is column A. Now if the first block of data is
6 rows deep, cell A1 contains 1. The next number to appear will be 2
in cell A7. Cells A2:A6 are blank.
Is there a workaround for this in your code ??
Thanks again.
Colwyn.

On Sep 11, 1:27*am, Gord Dibben <gorddibbATshawDOTca wrote:
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.