Ask Your Question

Autofill a column of merged cells

asked 2020-09-18 22:39:55 +0100

SteveK9 gravatar image

How does one autofill a column of cells that have been merged. In this case two rows per cell have been merged. Doing this the usual way fills the original cells prior to merging.

edit retag flag offensive close merge delete


Ctrl+C and Ctrl+V

Schiavinatto gravatar imageSchiavinatto ( 2020-09-19 00:27:01 +0100 )edit

@SteveK9 : I am not sure if i understand you. Could you explain (step by step) what you are trying todo. Thanks.

igorlius gravatar imageigorlius ( 2020-09-19 00:35:19 +0100 )edit

Concerning the terms: It's bad usage to talk of "merged cells". Spreadsheets always (logically) consist of all the cells they ever had. Cells never are actually merged. What's merged is the area of some cells which then is used as a larger display for whatever the topmost leftmost of the covered cells contains. The cells behind the merged area still exist, can have content, and can be referenced.

There is no "autofill for merged cells".
This may be one of the many reasons to consider merging evil.

To implement something looking like an autofill for merged areas as if they are cells will require user code.

Lupp gravatar imageLupp ( 2020-09-19 00:56:32 +0100 )edit

It may be 'bad usage', although the command has always said 'merge and center cells'. Not a spreadsheet aficionado, so I never realized that cells that have been merged, display only the top, leftmost cell, without decreasing the number of cells. I have someone else's spreadsheet with a column of 'merged area' cells (better?) with dates and it would have been nice to autofill them, rather than typing all the dates. But, I understand what you said, and it sounds like I am out of luck.

SteveK9 gravatar imageSteveK9 ( 2020-09-19 05:28:39 +0100 )edit

1 Answer

Sort by » oldest newest most voted

answered 2020-09-19 12:56:56 +0100

Lupp gravatar image

updated 2020-09-19 13:20:29 +0100

If you have a really big "project" of the kind (hundreds of entries) you can write a macro for the task.

You don't want or don't feel capable of doing so? Well, you may not be completely out of luck nonetheless.

To avoid programming you can work around the propblem this way:
-1- Insert temporarily an adjacent column to the one with merged cells (Yes. Everybody speaks this way.)
-2-Create the needed content for the mentioned "TopCells" there by formulas or any appropriate fill procedure.
-2a- It might be a good idea to make sure the cells adjacent to hidden ones are empty at the end. But if there aren't side-effects (due to hidden contents or unknown references) this isn't necessary.
-3- Copy this column (or the restricted range).
-4- Goto the first cell of the target.
-5- Call Paste Special... and select the wanted content types only. NO FORMATS!

See attachment: C:\fakepath\ask266876workaroundForFillingColumnWithMergedCells_1.ods

Concerning -2a- you may use filtering.

edit flag offensive delete link more


Thanks. I had tried this, but did not 'Paste Special', which works.

SteveK9 gravatar imageSteveK9 ( 2020-09-20 15:19:39 +0100 )edit
Login/Signup to Answer

Question Tools

1 follower


Asked: 2020-09-18 22:39:55 +0100

Seen: 39 times

Last updated: Sep 19