• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

VBA Insert New Rows

A

AlanFisher

New Member
Good afternoon,

I'm looking for a macro that will Insert an entire new row, in the row below the active cell. The active cell might be any cell in the row.

Cell contents should not be copied down but formulas, formatting, and conditional formatting should be.

I hope you can help. Thank you.

Regards, Alan
 
Hi,

Click on record macro and goto any cell insert row and copy down the above row. Then see the code is generated in the VBA editor, just change range("") to activecell.

Regards
Abdul Matheen
 
Abdul, thanks, I tried that first but it failed with "Method Range of Object Global Failed". It's picking up the row I selected when I recorded and not the next row below the active cell.
 
Narayan - thank you. I i'm not sure where your suggestion should fit inside the code. Currently, I have this, but there are syntax errors:

Sub Copy_One_Row_Below()
ActiveCell.Offset(1).EntireRow.Insert xlShiftDown, ActiveCell.Offset(1).

PasteSpecial Paste:=xlPasteFormulasAndNumberFormats

.Offset(1).PasteSpecial xlPasteFormats
Application.CutCopyMode = False

End Sub
 
Hi ,

The code will be :
Code: 
Sub Copy_One_Row_Below()
    ActiveCell.Offset(1).EntireRow.Insert xlShiftDown, ActiveCell
End Sub
Narayan
 
Hi Narayan, thank you. We are getting closer. Your code inserted the row correctly but I need the new row to contain the formulas, formatting, and conditional formatting from the row above. But not the cell contents.

I have tried this but I'm getting a compile error - Invalid or Unqualified reference:

Sub Copy_One_Row_Below()
ActiveCell.Offset(1).EntireRow.Insert xlShiftDown, ActiveCell
.Offset(1).PasteSpecial Paste:=xlPasteFormulasAndNumberFormats
.Offset(1).PasteSpecial Paste:=xlPasteFormats

End Sub
 
Hi ,

Try this :
Code: 
Sub Copy_One_Row_Below()
    With ActiveCell
         .Offset(1).EntireRow.Insert xlShiftDown, ActiveCell.Row
         .EntireRow.Copy
         With .Offset(1).EntireRow.Cells(1, 1)
              .PasteSpecial xlPasteFormulas
              .PasteSpecial xlPasteFormats
         End With
    End With
    Application.CutCopyMode = False
End Sub
Narayan
 
Narayan, thank you once again. This is almost exactly what I'm looking for. There is only one issue remaining and it is that data from the row above the newly inserted row, is copied down into the new row.

Is it possible, please, to insert the new row, with formulas but not data?

Regards, Alan
 
Hi ,

Does the newly inserted row contain formulae which result in values or does it contain values ?

If the row contains formulae , then if the formulae result in values being displayed , how can this be eliminated ?

Can you confirm what is happening , because the code is transferring formulae and formats , nothing else ?

Narayan
 
Hi

I've attached a workbook containing the area where I need to insert new rows. I've also appended your macro to a button - in row 3.

The only formulas are in Col T. To test it, I inserted new rows below row 15. You'll see the values in A15 and E15 have been copied down. They are not derived from a formula.

I hope this helps.

Alan
 

Attachments

  • Macro Test Workbook.xlsm
    14.3 KB · Views: 20
Hi ,

Now everything is clear !

Forget the macro ; just do the same set of actions using the Excel menus :

1. Place the cursor anywhere in row 15 , and after right-clicking , select Insert and Entire Row.

2. Select the entire row range A14:T14

3. Press CTRL C.

4. Come down to A15 and click on Paste Special ; select either Formulas or Formulas and number formats , and press ENTER.

5. The data in columns A , E and S is also pasted.

Narayan
 
Narayan, thank you for all your help. I know how to perform the task manually but I have been asked to find out how to use a macro to insert the row. It's for a colleague who isn't so sure of Excel and just wants to click a button.

So I guess my question is, can we not have a macro to copy the row above without the values, but including the formulas and the formatting. If this is not possible then so be it, but it would be "awesome" if it was possible to resolve this last issue.

Once again, thank you for all your help.

Kind regards, Alan
 
Hi ,

I am sure you know the manual steps required to do what ever you want done through a macro.

My point was that the macro is automating the steps you would have performed manually ; when the manual actions result in one outcome , how can you expect that the automated process will result in a different outcome ?

I am not sure , but I think it is impossible to do what you want done through a simple copy + paste. To do what you want done there are 2 ways , one before the copy + paste , and the other after the copy + paste.

Both methods involve going through each cell , checking whether it has a formula , and performing the copy + paste only if the cell has a formula.

A simple copy + paste of the entire range will not do what you want done.

Narayan
 
Thank you Narayan, I understand. I will use your solution as far as it takes us and complete the job manually. I appreciate all your help.

Kind regards

Alan
 
Hi I'm new to macros. found this thread while looking for a macro to insert a row of cells within a table (B6:H12). This inserts entire rows how can I modify it so that from any cell (G10 or any)within the table I can insert a row of cells within the table under the active cell (B11:H11). I also need to copy content of cells before the active cell (B10:F10) into the inserted row of cells. how can I combine relative referencing with absolute referencing?
 
You must log in or register to reply here.
Back
Top