# Excel 2002: insert rows automation



## hrisula (Sep 5, 2003)

Hi everybody,
My op system is Windows XP, and I have office 2002 installed
Here is my difficulty: I have a long excel sheet with a variety of formulas. When I insert new rows somewhere in the middle, I want to keep the same cell formatting (for numbers, currency.. etc) and the same formulas as the previous rows. Right now I have to do this task manually. Is there a way to automate it?
Thanking you in advance
Hrisula


----------



## Anne Troy (Feb 14, 1999)

Hrisula: I am working on getting you some VBA code to accomplish this task.


----------



## XL Guru (Aug 30, 2003)

As a test, I entered the formula

=ROW()

in A1 to A10, and formatted these cells as bold.

Then I entered

=A1*3

in B1, copied this down to B10, and formatted these as Number to 2 DPs.

So if I want to now insert 2 rows between row 4 & 5 I do this :

Select row 3 and 4 (click'n'drag on the row headers). Hover over the top border of A3. Hold down CTRL+Shift. Click'n'drag down & drop between row 4 and "old" row 5.

HTH,
Andy


----------



## Rollin_Again (Sep 4, 2003)

Instead of simply inserting a new row why don't you just copy the previous row and insert it where you want the new row to appear. All formulas and formats should remain entact on the copied row and then you can manually clear or change the contents of whichever specific cells you want. The process could be completely automated with a macro. If you post some sample data with some specific instruction I will be happy to help.


Rollin


----------



## XL Guru (Aug 30, 2003)

Re: Anne's VBA suggestion, something simple like

Selection.EntireRow.Copy
ActiveCell.Offset(1, 0).Rows("1:1").EntireRow.Insert Shift:=xlDown
Application.CutCopyMode = False

would insert "the row below". Then something like

ActiveCell.Offset(1,0).EntireRow.SpecialCells(xlCellTypeConstants, 23).ClearContents

would strip out any constants.

Rgds,
Andy


----------



## hrisula (Sep 5, 2003)

Thanks guys.

So, XL guru, I tried what you suggested and it works perfectly well if I add my rows at the end of the spreadsheet. If I were to add them in the middle, then I get an error in the first-following row, specifically in a particular cell where I calculate the balance, but the rest of the rows are correct. In other words, the first "previous" cell in the formula is incorrect, it remains as it was (original value) and does not change. Whatever pertains to the values of the cells of the same rows is modified correctly. Nevertheless it is a good method.

Rollin, your method is good too, especially when I insert rows at the end. But, suppose I have 150 rows and I forgot to make 3 entries between row 120 & 121 another 2 entries between 140 & 141, then it becomes a tedious job, especially if I have many spreadsheets. Is there a "universal" macro that can be inserted to all spreadsheets to resolve the problem, or does it have to be done specifically for each spreadsheet? 
Thanks for your offer though... here is a sample file

Anyhing to help me simplify my work will be appreciated


----------



## Anne Troy (Feb 14, 1999)

Here's some more code, by tommy_bak at my web:

```
Sub InsertARow() 
     'make new row
    ActiveCell.EntireRow.Insert Shift:=xlDown 
     'copy the row above
    ActiveCell.Offset(-1, 0).EntireRow.Copy Cells(ActiveCell.Row, 1) 
     'clear every cell in the new line that does not have a formula
    ActiveCell.EntireRow.SpecialCells(xlCellTypeConstants, 23).ClearContents 
End Sub
```


----------



## hrisula (Sep 5, 2003)

Thank you so much Dreamboat.
At the expense of sounding ignorant....
how do I proceed? where do I insert this code?
Can you please give me some clues...


----------



## Anne Troy (Feb 14, 1999)

Sure.
Open your file.
Hit Alt+F11 to open the Visual Basic Editor (VBE).
From the menu, choose Insert-Module.
Paste the code into the code window at right.
Hit the Save diskette toolbar button.
Close the VBE.
**
Go to Tools-Customize.
Hit the Commands tab.
Scroll down to Macros on the left.
On the right, drag the icon up to your toolbar.
Hit Close to close the Customize dialog box.
The first time you hit the toolbar button, it'll ask what macro you want to run, tell it "insertaRow".
After that, it should simply run that macro for you without asking anymore.

**If you want to test it before you bother making the toolbar button, go to Tools-Macro-Macros and double-click InsertaRow


----------



## Anne Troy (Feb 14, 1999)

Hold up!

Someone else came along and added an error handler. This is just in case you insert a row that has no existing data (i.e., you insert a row and then another right away). So use THIS code instead:


```
Sub InsertARow() 
     'make new row
    ActiveCell.EntireRow.Insert Shift:=xlDown 
     'copy the row above
    ActiveCell.Offset(-1, 0).EntireRow.Copy Cells(ActiveCell.Row, 1) 
    On Error Resume Next 
     'clear every cell in the new line that does not have a formula
    ActiveCell.EntireRow.SpecialCells(xlCellTypeConstants, 23).ClearContents 
End Sub
```


----------



## hrisula (Sep 5, 2003)

I followed all the steps exactly as you outlined them, everthing is perfectly OK. My only problem is the security.
I can't run the macro, an error message pops up saying that my security is high and macros are not enabled. I looked around but could not find any option that would let me enable them. 
One last question: how do I enable macros?

Thank you so much for your patience


----------



## Anne Troy (Feb 14, 1999)

Tools-Macro-Security.
Set it to medium.
Open the file.
Enable macros when asked.


No problem...


----------



## hrisula (Sep 5, 2003)

Perfect!!!
Works like a charm!!!
Thanks a million, I really appreciate your help.
Hrisula


----------



## Rollin_Again (Sep 4, 2003)

If you want your macro to be "Universal" you can create a seperate "Personal" workbook to hold the macro and have this workbook open each time you use Excel. To do this, open Windows Explorer and navigate to the following directory

*C:\Program Files\Microsoft Office\Office\Xlstart.*

Right-click in the window and choose *New --> Microsoft Excel Worksheet* and name the new workbook "Personal.xls"

Now double-click your new "Personal.xls" file to open it, add your macro code to it and then Save it. Next, choose
*Window --> Hide* and close the workbook. The next time you run Excel, "Personal.xls" will run in a hidden state and its macro will be available to any other workbook that is open.

Rollin


----------



## Mondamin (May 29, 2003)

Just found this... it's exactly the question I have for Excel 2003 (this was for 2002). Is there any difference in using this solution for 2003?


----------



## Zack Barresse (Jul 25, 2004)

Nope.


----------

