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



## PincivMa (Mar 14, 2004)

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


----------



## Jimmy the Hand (Jul 28, 2006)

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.


----------



## Zack Barresse (Jul 25, 2004)

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


----------



## PincivMa (Mar 14, 2004)

Hi guys

I did forget the attacment.

Here it is. I hope I attached it OK.

Mario


----------



## Jimmy the Hand (Jul 28, 2006)

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

```
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))* :up: was stolen from bomb #21 

Edit:

I'vo got it. So the copying part should be done this way:

```
c.EntireRow.Copy Destination:=Sheets("Sheet2").Rows(DestRow).Resize(RowCount)
```


----------



## bomb #21 (Jul 1, 2005)

Jimmy the Hand said:


> Range selection algorithm *Range("A2", Range("A" & Rows.Count).End(xlUp))* :up: was stolen from bomb #21


Which in turn was stolen from Zack.


----------



## Zack Barresse (Jul 25, 2004)

bomb #21 said:


> Jimmy the Hand said:
> 
> 
> > Range selection algorithm Range("A2", Range("A" & Rows.Count).End(xlUp)) was stolen from bomb #21
> ...


Which in turn was stolen from Bob Phillips, who originated the idea (a long time prior to the 2007 release).


----------



## PincivMa (Mar 14, 2004)

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


----------



## Jimmy the Hand (Jul 28, 2006)

Okay.


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

```
Sheets("Sheet1").Activate
```
No comment.


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


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


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

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


```
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*


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

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


----------



## PincivMa (Mar 14, 2004)

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


----------



## Zack Barresse (Jul 25, 2004)

There is only one problem with this line...


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


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


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


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


----------



## PincivMa (Mar 14, 2004)

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


----------



## Zack Barresse (Jul 25, 2004)

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.


----------

