Insert a new row with the same cell settings

Endiyo

New Member
Joined
Feb 17, 2025
Messages
4
Office Version
  1. 2019
Platform
  1. Windows
Hi
I have searched the whole web for this problem and i can't really find anything about it. I want to be able to insert a new row with the exact same settings from the row i am inserting from. The row i am inserting from has merged cells and when i insert a new row it will automatically add new columns and not merging the cells.

Please check the image:
(A) is what i want when i insert a new row and (B) is what's happening after inserting a new row. How can i solve this issue? Please help!

/Endiyo
 

Attachments

  • excel.png
    excel.png
    5.1 KB · Views: 4
1) select the whole row with formatting
2) copy it to clipboard (Ctrl+C)
3) select the row where you want it to be pasted
4) right click and select Insert copied cells
5) press Delete to delete copied contents (this step is not needed if the initial row was empty as on the screenshot you attached)

You can do it also with only few columns, not a whole row.

BTW Cell merging is a nice tool to compose elegant final report, but for working data ranges try to avoid merging. It usually makes a lot of problems.
 
Upvote 0
...Or
VBA Code:
Option Explicit

Sub InsertRowWithFormatting()
    Dim rng         As Range
    Set rng = ActiveCell.EntireRow

    rng.Offset(1).Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    rng.Copy
    rng.Offset(1).PasteSpecial Paste:=xlPasteFormats
    Application.CutCopyMode = False
    Set rng = Nothing
End Sub
Insert a new row with the same cell settings_Before.png
Insert a new row with the same cell settings_Macros.png
Insert a new row with the same cell settings_After.png

Good luck.
 
Upvote 0
1) select the whole row with formatting
2) copy it to clipboard (Ctrl+C)
3) select the row where you want it to be pasted
4) right click and select Insert copied cells
5) press Delete to delete copied contents (this step is not needed if the initial row was empty as on the screenshot you attached)

You can do it also with only few columns, not a whole row.

BTW Cell merging is a nice tool to compose elegant final report, but for working data ranges try to avoid merging. It usually makes a lot of problems.
First of all thank you for your answer, but this is not what i am trying to achieve. I want it to always give me merged cells like in the picture with only inserting a new row. We are a couple of people who are going to use this excel file and a lot of us isn't that good with computers unfortunately.
 
Upvote 0
Then think of macro proposed by MikeVol.
You can assign it to a selected shortcut so even "less experienced" users can use it.
 
Upvote 0
...Or
VBA Code:
Option Explicit

Sub InsertRowWithFormatting()
    Dim rng         As Range
    Set rng = ActiveCell.EntireRow

    rng.Offset(1).Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    rng.Copy
    rng.Offset(1).PasteSpecial Paste:=xlPasteFormats
    Application.CutCopyMode = False
    Set rng = Nothing
End Sub
View attachment 122442View attachment 122443View attachment 122444
Good luck.
Thank you for your answer!
Can you please show me this step by step instead? I don't really understand where and how i am going to put that codes?

/Endiyo
 
Upvote 0
Sorry, but I don't have free time to teach anyone here. There is plenty of information on the Internet about what and how, you just have to set a goal. You can start with this article, for example. Good luck.
 
Upvote 0
Sorry, but I don't have free time to teach anyone here. There is plenty of information on the Internet about what and how, you just have to set a goal. You can start with this article, for example. Good luck.
No problem at all, i will google it. Thank you for your help and i wish you a good day/evening/night!

/Endiyo
 
Upvote 0
I think pre link given by @MikeVol shall be enough to start. I'd add only that you will find a place to add a shortcut to call a macro from keybord here (this is the screenshot from the mentioned above link to ablebits:

1739825690689.png


BTW. If you find the code working, it would be a good idea to mark Mike's answer as a solution. See the link in my signature below how to do it.
 
Upvote 0

Forum statistics

Threads
1,226,771
Messages
6,192,924
Members
453,767
Latest member
922aloose

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top