How do I automatically copy/paste ?

[colwyn]

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.

 

[Gord Dibben]

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

 

[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

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.

 

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

 

[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

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 Dibben]

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

 

[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

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.

 

[Gord Dibben]

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

 

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

 

[Gord Dibben]

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

 

[colwyn]

Thanks Gord Bet you'll be glad to hear that !!
Thanks for all.
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

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.

 

[Gord Dibben]

Can you let Don know you are done with this?

You have two threads going with the same needs.


Gord

Thanks Gord Bet you'll be glad to hear that !!
Thanks for all.
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

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,

<[email protected]> 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.

 

[colwyn]

Yes I did that yesterday Gord.
Colwyn.

Can you let Don know you are done with this?

You have two threads going with the same needs.

Gord

Thanks Gord Bet you'll be glad to hear that !!
Thanks for all.
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
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
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 Dibben]

I saw that.

Thanks

Yes I did that yesterday Gord.
Colwyn.

Can you let Don know you are done with this?

You have two threads going with the same needs.

Gord

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

<[email protected]> 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.