Merged fields in Excel don't stretch to fit the text

Anonymous
2015-01-12T15:41:01+00:00

Hi

I have an excel 2010 spreadsheet that I use to Macro out certain sheets into its own file and within that sheet I have 3 columns but some of the rows are merged across those 3 columns.  When I macro out the row does not auto fit the text because it is merged.  Is there anything I can add within my macro to automatically get the row to fit the text?

Thanks

Micheemoo

Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments
Answer accepted by question author
Anonymous
2015-01-13T15:54:25+00:00

What I posted is worksheet event code.

Right-click on the sheettab and select "View Code"

Paste the code as is into that module.

Return to the sheet and make sure merged cells are formatted to wrap text.

When you type text into a merged cell then hit Enter the autofit will take place.

If you already have text in a cell you can F2>Enter to trigger the code.

Gord

Was this answer helpful?

0 comments

10 additional answers

Sort by: Oldest
Most helpful Newest Oldest
  1. Anonymous
    2015-01-12T17:10:28+00:00

    To autofit row (assume that you have set wrap text)

    Rows("5:5").EntireRow.AutoFit

    To autofit column

    Columns("D:D").EntireColumn.AutoFit

    Was this answer helpful?

    0 comments
  2. Deleted

    This answer has been deleted due to a violation of our Code of Conduct. The answer was manually reported or identified through automated detection before action was taken. Please refer to our Code of Conduct for more information.

    Comments have been turned off. Learn more

  3. Anonymous
    2015-01-12T18:22:31+00:00

    Thanks for your correction, I totally forgot merged cells cannot autofit.

    Was this answer helpful?

    0 comments
  4. Anonymous
    2015-01-12T18:40:32+00:00

    One more reason that the concept of merged cells was a poor decision many years ago.

    I went on record in the news groups around 2002 with my rant that merged cells were the "spawn of Satan"

    They cause more problems with Excel than any other feature I can think of.

    Copying, cutting, pasting, sorting, filtering and autofitting are all adversely

    affected by merged cells.

    Gord

    Was this answer helpful?

    0 comments