1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Inserting specific number of rows in Excel and copying data in those inserted rows

Discussion in 'Business Applications' started by PincivMa, Dec 6, 2006.

Thread Status:
Not open for further replies.
Advertisement
  1. PincivMa

    PincivMa Thread Starter

    Joined:
    Mar 13, 2004
    Messages:
    378
    Hi There

    It has been quite a while since I asked for help. I wonder if the following is doable as an Excel macro. The attached is only a demo with only 2 fields and a few rows of data. The real worksheet has more fields and around 3,000 rows.

    Sheet 1 contains the data in its initial state and sheet 2 contains data after the macro is run. You will see from sheet 2 that the field labeled QUANTITY determines the number of rows to insert and copy the correct data into those inserted rows. I want the entire row to be copied down as opposed to only the data, since I have more columns than shown here. Is this possible to do? Can anyone help me with writing a macro, since I have over 3,000 rows to do.

    Thanks for all your help. You guys are fantastic.

    Mario
     
  2. Jimmy the Hand

    Jimmy the Hand

    Joined:
    Jul 28, 2006
    Messages:
    1,223
    Hi Mario,

    As for your question, yes, it could definitely be done with macro, no problem. But you forgot to attach the sample workbook. Please update.
     
  3. Zack Barresse

    Zack Barresse

    Joined:
    Jul 25, 2004
    Messages:
    5,452
    I too am curious, but in your sample file, be sure to include all of your columns and a before and after view of your data (sheet1 and sheet2 look).
     
  4. PincivMa

    PincivMa Thread Starter

    Joined:
    Mar 13, 2004
    Messages:
    378
    Hi guys

    I did forget the attacment.

    Here it is. I hope I attached it OK.

    Mario
     

    Attached Files:

  5. Jimmy the Hand

    Jimmy the Hand

    Joined:
    Jul 28, 2006
    Messages:
    1,223
    I suppose you use the word "insert" in a classic, database related sense, which is actually appending rows to the table. If my assumption is correct, then one possible way to get the job done is
    Code:
        Dim c As Range, DestRow As Long, I As Long, RowCount As Long
        Sheets("Sheet1").Activate
        For Each c In Sheets("Sheet1").Range("A2", Range("A" & Rows.Count).End(xlUp))
            RowCount = c.Offset(0, 4)
            DestRow = Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Row + 1
            c.EntireRow.Copy Destination:=Sheets("Sheet2").Rows(CStr(DestRow) & ":" & CStr(DestRow + RowCount - 1))
        Next c
    Maybe row reference Rows(CStr(DestRow) & ":" & CStr(DestRow + RowCount - 1)) can be done with purely numerical values, I haven't found it out yet.

    Range selection algorithm Range("A2", Range("A" & Rows.Count).End(xlUp)) (y) was stolen from bomb #21 ;)


    Edit:

    I'vo got it. So the copying part should be done this way:
    Code:
    c.EntireRow.Copy Destination:=Sheets("Sheet2").Rows(DestRow).Resize(RowCount)
     
  6. bomb #21

    bomb #21

    Joined:
    Jul 1, 2005
    Messages:
    8,546
    Which in turn was stolen from Zack. ;)
     
  7. Zack Barresse

    Zack Barresse

    Joined:
    Jul 25, 2004
    Messages:
    5,452
    Which in turn was stolen from Bob Phillips, who originated the idea (a long time prior to the 2007 release). ;)
     
  8. PincivMa

    PincivMa Thread Starter

    Joined:
    Mar 13, 2004
    Messages:
    378
    Hi Jimmy the Hand

    I tried your macro and it worked great. However, I understand only part of your code. If it not too much trouble can you explain what each line does so that I understand the code better? I thought that you would somehow insert rows as specified by column 4 and then copy down the entire row in the empty spaces. That is what I tried to do but I did not know how to insert the proper number of rows as specified by column 4. Your code seems foreign to me. I did go into the macro and pressed F8 to see what each line did but the cursor did not move.

    Any explanation that you can offer me is greatly appreciated.

    Thanks,

    Mario
     
  9. Jimmy the Hand

    Jimmy the Hand

    Joined:
    Jul 28, 2006
    Messages:
    1,223
    Okay.

    Code:
    Dim c As Range, DestRow As Long, I As Long, RowCount As Long
    Declaring variables. Variable I As Long is in fact unnecessary. It was used in a previous version, but not anymore. Can be removed.
    Code:
    Sheets("Sheet1").Activate
    No comment.

    Code:
    Sheets("Sheet1").Range("A2", Range("A" & Rows.Count).End(xlUp))
    This expression returns a range. First cell of the range is A2, last cell is the bottommost cell in column A that has any value in it.

    Code:
    For Each c In Sheets("Sheet1").Range("A2", Range("A" & Rows.Count).End(xlUp))
    For Each object In Collection - is one type of loops supported in VBA. It takes every element (object) in the given collection, and executes the core code of the loop on them. This particular For Each... loops through every cell in the range I described above, row by row, and does something with it. This something is described below.

    Code:
    RowCount = c.Offset(0, 4)
    RowCount is a long type variable declared in the first line.
    c is the current object the loop is processing, in other words, the currently processed cell of the range.
    Offset method is used for relative cell addressing. See Excel Help for more details.
    c.Offset(0,4) designates the 4th cell to the right from the current cell (c). Also, it means the cell's value as well, by default.
    So, RowCount = c.Offset(0, 4) is the equivalent of RowCount = c.Offset(0, 4).Value
    To sum up, variable RowCount gets the value of the QUANTITY field of the current row, because it's in the 5th column, which is the same as 4 cells to the right from the 1st cell.
    Code:
    DestRow = Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Row + 1
    DestRow is also a variable.
    Range("A" & Rows.Count).End(xlUp).Row + 1 returns the rowindex of the first empty cell in column A, after the last used one.
    So, DestRow will hold the index of the first empty row on Sheet2, that is, the destination of the next copy action.

    Code:
    c.EntireRow.Copy Destination:=Sheets("Sheet2").Rows(DestRow).Resize(RowCount)
    You might have noticed in my post the edited part. There I suggested to use this line for copy action. So I will explain this one, not the original. This is better, anyway.
    c.EntireRow returns a range that is the entire row of the currently processed cell.
    c.EntireRow.Copy copies this row to the range defined as Destination.
    Now, DestRow is the next empty row on Sheet2, as I said before. Resize method is used to change the size of the selected range, so that it includes more cells or less cells than before. See Excel Help for description.
    Rows(DestRow).Resize(RowCount) will enlarge the range so that the number of rows will be the equal of RowCount. E.g. if RowCount = 5 and DestRow=2, then Rows(DestRow).Resize(RowCount) will return 5 rows: row #2, #3, #4, #5, and #6.

    A little sidetrack:
    Study a bit how Copy/Paste works in Excel. Select one single cell with a value, press Ctrl+C. Then select a larger area on the sheet, with a dozen cells in it. Press Ctrl+V. You'll see that content of the single cell got copied into each cell of the selected range. It is the same with rows. Select one single row, press Ctrl+C. Then select an array of rows and press Ctrl+V. The single row gets multiplicated. This feature is exploited in the code I provided.
    So c.EntireRow.Copy Destination:=Sheets("Sheet2").Rows(DestRow).Resize(RowCount)
    copies the current row to the first empty row of Sheet", and repeats copy into the underlying rows as many times as the value of RowCount

    Code:
    Next c
    End of loop, look for the next cell.


    I hope it was informative enough. I doubt I can make it any clearer.
     
  10. PincivMa

    PincivMa Thread Starter

    Joined:
    Mar 13, 2004
    Messages:
    378
    Hi Jimmy the Hand

    You did a great job in describing what each line of code does. You should each Excel Programming at a college or university.

    Thanks for taking the time to explain the code to me.

    Mario
     
  11. Zack Barresse

    Zack Barresse

    Joined:
    Jul 25, 2004
    Messages:
    5,452
    There is only one problem with this line...

    Code:
    For Each c In Sheets("Sheet1").Range("A2", Range("A" & Rows.Count).End(xlUp))
    ... and that is the reference to the second range. It will be a problem if you are not on sheet1. Of course the line directly above it negates this as it activates that sheet. I do not recommend activating sheets as it is not necessary and it eats available memory/resources. Instead, explicitly reference the sheet(s) being used. This is done for the first series of Range objects, but not the second. That line of code should then be ...

    Code:
    For Each c In Sheets("Sheet1").Range("A2", Sheets("Sheet1").Range("A" & Sheets("Sheet1").Rows.Count).End(xlUp))
    Looks kind of long, and it is. This is why I generally set the sheet to a variable or use a With statement. The With statement would make it look like this ...

    Code:
        Dim c As Range, DestRow As Long, RowCount As Long
        With Sheets("Sheet1")
            For Each c In .Range("A2", .Range("A" & .Rows.Count).End(xlUp))
                RowCount = c.Offset(0, 4)
                DestRow = Sheets("Sheet2").Range("A" & .Rows.Count).End(xlUp).Row + 1
                c.EntireRow.Copy Destination:=Sheets("Sheet2").Rows(CStr(DestRow) & ":" & CStr(DestRow + RowCount - 1))
            Next c
        End With
    An example of setting the two sheets to variables would be ...

    Code:
        Dim ws1 as worksheet, ws2 as worksheet
        Dim c As Range, DestRow As Long, RowCount As Long
        Set ws1 = Sheets("Sheet1")
        Set ws2 = Sheets("Sheet2")
            For Each c In ws1.Range("A2", ws1.Range("A" & ws1.Rows.Count).End(xlUp))
                RowCount = c.Offset(0, 4)
                DestRow = ws2.Range("A" & ws2.Rows.Count).End(xlUp).Row + 1
                c.EntireRow.Copy Destination:=ws2.Rows(CStr(DestRow) & ":" & CStr(DestRow + RowCount - 1))
            Next c
        End With
    If you notice with the Rows.Count, I also added a sheet reference for that. This is mainly because if this code is run and there is not a worksheet active it will fail, as it assumes it is looking at the active sheet. This becomes most prevalent when writing code for add-ins and other people, etc.

    Btw, I agree with Mario, nice explanation Jimmy. :)

    HTH
     
  12. PincivMa

    PincivMa Thread Starter

    Joined:
    Mar 13, 2004
    Messages:
    378
    Hi Firefytr

    Thanks for your contribution to the code. I tested it out and it works quite well. Where do you guys learn all this good programming VBA?

    Mario
     
  13. Zack Barresse

    Zack Barresse

    Joined:
    Jul 25, 2004
    Messages:
    5,452
    Trial by fire. :)

    I started in Dec 2003 with a question at MrExcel.com, the only thing I knew about Excel was the SUM function and Conditional Formatting. Just taking the time to read others posts and start posting where I knew I could lend a hand. So helping people was how I learned. Others read books, some go to school. You'll learn as fast as you put forth the effort though, I'll tell you that. You could be an excellent programmer in a few short months, or it could take a few years, it all depends on you.

    One thing that helps, is watching people like Andy, Jimmy, OBP, ChuckE, others from various forums such as xld, DRJ, pennysaver, mdmackillop, johnske, colo, Rembo, dk, brettdj, Ken Puls, Anne Troy, the list goes on and on. Spread out, find some good resources, they're abundant on the web. That is why I frequently visit 5-6 forums a month. I'm learning constantly. :)
     
  14. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/524552

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice