Copy Merged Cells When Inserting New Rows

[miketurn]

Hello Everyone,

I am currently using Excel 2000 and I was wondering if there was a way to copy how cells are merged in a row when inserting new rows.

For example if you have a row that has a certain color fill say yellow and the next cell does not have a color fill it is white and you select it (the white cell) and use the insert row tool, Excel will create a new row and it will be yellow.

But

Lets say Column A and B are merged together and you use the "Insert Row" tool, Excel will create a new row, but this new row will not have Column A and B merged anymore, they will be separate.

Does anyone know of a way (some kind of setting) that will allow you to copy the merged cells from the row above when inserting new rows?


I have found a couple solutions but they aren't as smooth as I would like, I would like this to just happen automatically as stated above.

Methods I have found...
1.) There is a Merge Across tool that allows you to highlight multiple row/columns and it will merge just the rows.

2.) You can copy the merged cells (if they are blank) and paste them


I am sure there a macro can fix it, but I don't know if it would be worth it (I am still willing to try a macro if someone has one already made, but it may be more complicated then it is worth to apply it), I am hoping for a simple setting somewhere, but I don't think there is one for this?


If anyone has any ideas, please share.
Thank You to anyone who reads this.

 

[baitmaster]

First things first, merged cells are a pain. They can look pretty in certain cases but they cause problems as you get more advanced e.g. writing VBA code. I rarely use them because they are nothing but trouble.

That said, there are various things you could consider
1 Rather than have merged cells, select "centre across selection" from the Alignment tab of the Format Cells menu (I don't recall how to find this in Excel 2000). The cells will still be separate, but text written in the leftmost cell will be aligned across all cells formatted in this way (this would be my preferred option)
2 copy row above, select row below and hit insert copied cells. This will insert a new line with the formats you want but take all data & formulas with it too
3 insert row. Select row above, hit the "format painter" button (looks like a paintbrush) and select your new row
4 a macro-based solution that performs a similar action to 2 or 3 above. But I don't see a need for this, one of the others should do it for you

 

[miketurn]

Thank You for your response,

I agree they are a pain to deal with, I don't use them a ton but sometimes I have to.

Most of those ideas I have tried, I forgot to mention in my post above that I tried the Format Painter option also.
I have never tried your 1st suggestion, I just did, it didn't work that well for me because it centers the text.

Anyways, there are quite a few options, but I was hoping that I was just missing a "copy merge cells from above row when inserting rows" type of setting somewhere that I had overlooked

Thank You again for your help

 

[miketurn]

Does anyone know if there is some kind of coding that would allow an Excel macro to be able to analyze the row above a selected rows merged cells and replicate them when creating a new row?

Which is how Excel adds new rows in.
1.) You select a row
2.) hit the "Insert Rows" tool button
3.) it inserts the row above the row you have selected

Funny thing is if the row above has a background color assigned to it, Excel knows to copy that information when inserting the new row, but not the merged cell structure.

If anyone has any existing macros to achieve this, please let me know.
Thank You to anyone who reads this.