Macro - Insert Row with cell merge and formula copy

[Eios000]

Afternoon all,

With much searching occurring around the interwebs I cannot find an answer to this, so here's what I would like to achieve!

Macro InsertRow()

When new row is inserted below the last used row, I need the following to occur within the worksheet "Register".

1. The cell above the new row in Column A is merged,
2. The formatting and formula in Column P is copied into the new row, and
3. All other values apart from the previous two mentioned are cleared

Thanks for your help!

-Kyle.

 

[Zot]

The cell above the new row in Column A is merged with what cell(s)? Maybe sample will give clearer picture.

 

[Zot]

If you want to keep everything line the previous row, you can just copy and paste the previous row. Then clear the contents of new row using

<range>.ClearContents

Then you can merge the cell. I presumed you want to merge column A with previous one like:
Set rngA = Range("A" & <newRow>)
Range(rngA, rngA.Offset(-1)).Merge

 

[Eios000]

The cell above the new row in Column A is merged with what cell(s)? Maybe sample will give clearer picture.

Hey Zot,

I've added some pictures of the intended outcome, hopefully this clarifies what I'm trying to achieve.

-Kyle.

 

[Zot]

Hey Zot,

I've added some pictures of the intended outcome, hopefully this clarifies what I'm trying to achieve.

-Kyle.

It looks like what I proposed in Post #3

I have no idea the steps you take to fill the table. So, I have no clue where the code need to start from

 

[Zot]

Here is simple semi-auto approach

Select the last populated row and run the macro below. See if this works

Sub InsertRow()

ActiveCell.EntireRow.Copy ActiveCell.Offset(1)
ActiveCell.Offset(1).Select
ActiveCell.EntireRow.SpecialCells(xlCellTypeConstants, 23).Clear
Set rngA = Range("A" & ActiveCell.Row)
Range(rngA, rngA.Offset(-1)).Merge
rngA.VerticalAlignment = xlCenter

End Sub

 

[Eios000]

Thanks for your help Zot! I ended up not going with the merge option, turns out with other macros this was creating issues.

 

[Zot]

Thanks for your help Zot! I ended up not going with the merge option, turns out with other macros this was creating issues.

Yes, avoid at all cost using merged cell with macro. Just inviting headache only