I need a macro to copy and insert rows

July 2, 2013 at 03:04:45
Specs: Windows 7
I have 80,000 rows of data in Excel 2010 with columns A - AV. Many of the far-right columns are formulas.

I have a filtered view by one of the 500 project names. To make this task easier, I could sort so that all this specific project name's data are on contiguous rows, but would prefer not to have to.

I have just run a macro to select every other row through columns A - AA. Now I need a copy of each row's selected data to be inserted in a new row just above it.

I saw a macro that would do this but only one row at a time, and you must select the entire row.

Thank you,


See More: I need a macro to copy and insert rows

Report •


#1
July 2, 2013 at 04:31:54
First, I have edited your subject line to make it more relevant to the content if your post. When you use a generic subject line like your original one, no one can tell what the question is about.

Second, if you already have a macro that does part of what you need, you should post it so that we don't have to start from scratch.

Before posting your code, please click on the following line and read the instructions found via that link. Thanks.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#2
July 2, 2013 at 04:52:43
Dear Moderator,

Below is the requested code that enables the selection of every nth row, which accomplishes the first half of my needed task in Excel.

Note To change the number of rows between each selected row, change the value of the RowsBetween variable; for example, if you want to select every fourth row, set RowsBetween to 4.

Sub SelectEveryNthRow()
    ' Initialize ColsSelection equal to the number of columns in the
    ' selection.
    ColsSelection = Selection.Columns.Count
    ' Initialize RowsSelection equal to the number of rows in your
    ' selection.
    RowsSelection = Selection.Rows.Count
    ' Initialize RowsBetween equal to three.
    RowsBetween = 2
    ' Initialize Diff equal to one row less than the first row number of
    ' the selection.
    Diff = Selection.Row - 1
    ' Resize the selection to be 1 column wide and the same number of
    ' rows long as the initial selection.
    Selection.Resize(RowsSelection, 1).Select
    ' Resize the selection to be every third row and the same number of
    ' columns wide as the original selection.
    Set FinalRange = Selection. _
       Offset(RowsBetween - 1, 0).Resize(1, ColsSelection)
    ' Loop through each cell in the selection.
    For Each xCell In Selection
        ' If the row number is a multiple of 3, then . . .
        If xCell.Row Mod RowsBetween = Diff Then
            ' ...reset FinalRange to include the union of the current
            ' FinalRange and the same number of columns.
            Set FinalRange = Application.Union _
                (FinalRange, xCell.Resize(1, ColsSelection))
        ' End check.
        End If
    ' Iterate loop.
    Next xCell
    ' Select the requested cells in the range.
    FinalRange.Select
End Sub


Report •

#3
July 2, 2013 at 09:09:05
I'm a little confused as to how you are using the macro, based on the wording of your OP.

You said:

"I have just run a macro to select every other row through columns A - AA."

Since the code is written to select every other row based on the initial Selection, I have to assume that you are selecting Columns A:AA before you run the code. Will you always want Columns A:AA copied to the new rows or will each run be based on the actual columns selected?

In addition, it appears that your selection must include either Row 1 or Row 2, otherwise the code doesn't select every nth Row. Is that correct?

Finally, related to the Copy operation itself, you said:

"I saw a macro that would do this but only one row at a time, and you must select the entire row."

Since Excel itself can not "Copy-Insert-Paste" a range that contains noncontiguous cells, I don't believe that you are going to be able to force VBA to do the Copy-Insert-Paste all at once. I believe that the only way to do it is to loop through each row in the newly selected range.

As far as the code you saw that forces you to copy entire row, that can be dealt with by simply deleting the extraneous data after the Paste.

Before I offer any code, please post a little bit more information related to how you use the worksheet. If you are always Selecting the same Rows/Columns, then it might be easier to simply hardcode that Range into the macro and eliminate the need to manually Select the Range.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

Related Solutions

#4
July 3, 2013 at 08:48:32
More on how I use the worksheet: I am NOT always selecting the same rows/column. I have 500 contiguous rows (say worksheet row numbers 18712 - 19211) of columns A - AV. I need to add a line on every 3rd rows, effectively expanding it from 500 to 750 rows. I need the data from columns A-AA on each new row to match the data on every second row of the initial 500. Columns AB-AV of the new 250 should be blank.

Yes, I AM selecting columns A-AA before I run the code. However, each run will be based on the actual columns selected within the finite table.

Also, I am NOT necessarily selecting either row 1 or row 2 (worksheet row number that is). I AM of course selecting row 1 and 2 of the Selection so that the nth row code works. I have a filtered view of, say, 500 rows, and most often EXCLUDING worksheet rows 1 or 2, and even so, the code DOES work to select every nth row.

To have to loop through each of the 500 or so rows is much more work than setting up the selection from the outset as contiguous rows. I would say that we should assume that I would do that first. (Note though that the COM add in called "Kutools" also enables the selection of every nth row, and in addition, DOES allow up to 18 copy and pastes of non-contiguous rows as long as one selects the 18 paste-to locations individually, so this leads me to believe there is some capability for excel to at least copy and paste a range of non-contiguous cells (without the insert option if that makes a difference).

You write:
As far as the code you saw that forces you to copy entire row, that can be dealt with by simply deleting the extraneous data after the Paste.

Well, the extraneous data would be located on columns AB-AV on every 3rd row in the midst of 500 other rows … how would I isolated that data before I delete it, as it is a copy the data from the row below it... unless I loop through each 3rd row individually, which i really want to avoid ?

Thank you,


Report •

#5
July 3, 2013 at 10:03:06
Ok, lots to deal with here, and not necessarily in order. Keep in mind that I can only run the code against a test workbook that I set up, not your actual workbook, unless you send it to me. That said:

re: "I have a filtered view of, say, 500 rows, and most often EXCLUDING worksheet rows 1 or 2, and even so, the code DOES work to select every nth row"

When I run the code against an unfiltered workbook, it only Selects every nth row if Row 1 or 2 is included in the selection. Otherwise it only selects the first row of the original selection. At least that is what my limited testing has shown. Perhaps the filtering has something to do with that, but as I said earlier, I can't replicate your exact environment without the workbook.

re: "the COM add in called "Kutools" also enables the selection of every nth row, and in addition, DOES allow up to 18 copy and pastes of non-contiguous rows as long as one selects the 18 paste-to locations individually, so this leads me to believe there is some capability for excel to at least copy and paste a range of non-contiguous cells "

Keep in mind what an add-in actual is.

Stolen without permission from: http://fontstuff.com/vba/vbatut03.htm

"An Excel Add-In is a file (usually with an .xla or .xll extension) that Excel can load when it starts up. The file contains code (VBA in the case of an .xla Add-In) that adds additional functionality to Excel, usually in the form of new functions."

While it may appear that Excel is performing a Copy/Paste of non-contiguous cells, I'll wager that what is really happening is that the VBA code inside the Add-in is looping through the selected cells and doing the Copy/Paste one at a time. That is what I meant when I mentioned this in my previous post. The code could still select every nth row all at once as it does, but the address of newly selected range would be used to allow VBA to loop through it and do a Copy/Insert-Paste one row at a time. I'm not suggesting that the user needs to be involved any more than to select the original range to work on. VBA will be doing the looping.

re: "Well, the extraneous data would be located on columns AB-AV on every 3rd row in the midst of 500 other rows how would I isolated that data before I delete it, as it is a copy the data from the row below it... unless I loop through each 3rd row individually"

I don't think you can avoid looping through each row in the new selection (e.g. every 3rd row) for the reasons I've stated above. I could be wrong, but I do not believe that it is possible to do a mass Copy/Paste (Insert or not) of non-contiguous cells with Excel. Try this:

Select A1, A3 and A5, Ctrl-c to Copy
Select A2, A4 and A6, Ctrl-v to Paste

Read the error message that appears. If you can't do it in Excel, you aren't going to force VBA to to do it.

Therefore, in the same loop that does the Copy/Insert-Paste of the entire row, we would simply add another instruction to delete all data beyond the last column that we want. This would be done right after the Insert/Paste operation.

Since I don't have a copy of your workbook, and don't have a clear understanding of how you select the filtered range to "Insert/Copy" it's kind of hard for me to create a macro that will do exactly what you want.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#6
July 8, 2013 at 07:57:54
Ok...

I really believe a test workbook would be no worse than the actual data in this case.

I will make sure the rows are contiguous before I start the code.

Skip the code for the selection of every nth row. I am just interested in inserting a row above every previously selected row, and inserting a copy of the row beneath into the new row, and automatically deleting columns AE through, say, 100 columns to the right of column AE in the new row, (or even to to the last possible column to the right).
Hope it makes sense.
Thank yo so much for your efforts.


Report •

#7
July 8, 2013 at 18:37:08
Try this code.

The first change I made was to add a InputBox so that you can enter the value for the RowsBetween variable instead of having to edit the code each time. I left the original code in place and just commented it out. Use whichever method you prefer.

The second change is the snippet of code at the bottom of the macro entitled "Copy/Insert Code".

What this section does is build an array that contains the addresses of each of the Rows that were Selected by the SelectEveryNthRow code. It then loops through that array, copies each row to the row above and then clears the data in Column AE through the last column of the inserted row.

The length of time required for the code to complete will be based on the number of Rows in the original selection and how fast your machine is. As I said in my previous posts, the code has to loop through each row in the selection in order to perform the Copy/Insert/Clear operation.

My machine took about 4 seconds to copy every other row with an original selection of 500 rows. (250 copy/insert/clear operations)

Let me know...

Sub SelectEveryNthRow_CopyInsert()
    ' Initialize ColsSelection equal to the number of columns in the
    ' selection.
    ColsSelection = Selection.Columns.Count
    ' Initialize RowsSelection equal to the number of rows in your
    ' selection.
    RowsSelection = Selection.Rows.Count
    ''''''Initialize RowsBetween equal to three.
    ''''''RowsBetween = 2
    ' Get RowsBewteen Value From Users
    RowsBetween = Application.InputBox("Enter RowsBetween Value")
     If RowsBetween = False Then Exit Sub
    ' Initialize Diff equal to one row less than the first row number of
    ' the selection.
    Diff = Selection.Row - 1
    ' Resize the selection to be 1 column wide and the same number of
    ' rows long as the initial selection.
    Selection.Resize(RowsSelection, 1).Select
    ' Resize the selection to be every third row and the same number of
    ' columns wide as the original selection.
    Set FinalRange = Selection. _
       Offset(RowsBetween - 1, 0).Resize(1, ColsSelection)
    ' Loop through each cell in the selection.
    For Each xCell In Selection
        ' If the row number is a multiple of 3, then . . .
        If xCell.Row Mod RowsBetween = Diff Then
            ' ...reset FinalRange to include the union of the current
            ' FinalRange and the same number of columns.
            Set FinalRange = Application.Union _
                (FinalRange, xCell.Resize(1, ColsSelection))
        ' End check.
        End If
    ' Iterate loop.
    Next xCell
    ' Select the requested cells in the range.
    FinalRange.Select
''
'Copy/Insert Code
Dim selRows() As String
Dim rRow As Range
Dim eRow As Long
'Build Array with Addresses of Selected Rows
  For Each rRow In Selection.Rows
    eRow = eRow + 1
    ReDim Preserve selRows(eRow)
    selRows(eRow) = rRow.EntireRow.Address
  Next rRow
'Turn off ScreenUpdating to speed up code
  Application.ScreenUpdating = False
'Loop through Address Array in Reverse order
    For eRow = UBound(selRows) To 1 Step -1
'Copy Row, Insert Copy
       Range(selRows(eRow)).EntireRow.Copy
       Range(selRows(eRow)).EntireRow.Insert
'Clear Data from Column AE through Last Column
       Range(selRows(eRow)).Range(Cells(31), Cells(Columns.Count)).ClearContents
    Next eRow
  Application.ScreenUpdating = True
End Sub

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#8
July 9, 2013 at 00:59:02
I don't know why this code worked on your machine but it's giving me an error:
"Runtime error '1004' app-defined or obj-defined error

When I debug, it highlights the section in the code located 6 lines from the bottom where you write in the code:

Range(selRows(eRow)).EntireRow.Insert

Reminder that I sorted so that my selection was only of contiguous rows.

Wait a minute!. I just realized that the whole range can be completed doing the following:

1. Sort that selection is on contiguous rows
2. Manually select the selection
3. Using Kutools "Insert Tools", insert blank rows in desired interval
4. Manually select the selection from column A through column AD
5. Using Kutools, select interval rows of the desired copy-from rows
6. Using Kutools "Copy Ranges" tools, just use it, hit ok and then all you need to do is select the top blank cell in column A

This leads me to believe that this can be coded into one step w/VGA, no?


Report •

#9
July 9, 2013 at 04:13:09
As I said earlier, without a copy of your workbook, and the exact process that you use to select rows, there are variables that may impact how the code works.

I am not familiar with Kutools so I can't answer any questions related to its functionality.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#10
July 9, 2013 at 04:27:57
May I send you a copy of my worksheet? If so, please instruct how you prefer I do that.

Report •

#11
July 9, 2013 at 05:40:00
I have sent you an email address via PM. Please do not share the email address with anyone. It is temporary and will be deleted once this issue is resolved.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#12
July 25, 2013 at 06:42:40
It took 2 weeks but i sent you 2 days ago the excel file via the PM link you sent. Did you get it?

Report •

#13
July 25, 2013 at 06:50:12
I will check this evening (EST).

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#14
July 26, 2013 at 06:10:30
I received the file but did noy have a chance to work on it. I hope to have some free time this weekend.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •


Ask Question