Share via

Auto Height for Merged Cells w/Wrap Text - VBA

Anonymous
2020-03-26T18:39:05+00:00

I found the following code in the community, but looking to see if it can be applied to ALL worksheets within the workbook. It looks like as written, I would have to copy & paste it into each individual sheet.  Also, I saw this same code in a different thread but it mentioned password protection. If I pla on protecting my workbook, would I need to add those additional lines in there? Any help would be greatly appreciated. *I am brand new to VBA, so not even sure if it's possible.

Private Sub Worksheet_Change(ByVal Target As Range)

 Dim NewRwHt As Single

 Dim cWdth As Single, MrgeWdth As Single

 Dim c As Range, cc As Range

 Dim ma As Range

With Target

 If .MergeCells And .WrapText Then

 Set c = Target.Cells(1, 1)

 cWdth = c.ColumnWidth

 Set ma = c.MergeArea

 For Each cc In ma.Cells

          MrgeWdth = MrgeWdth + cc.ColumnWidth

 Next

 Application.ScreenUpdating = False

      ma.MergeCells = False

       c.ColumnWidth = MrgeWdth

        c.EntireRow.AutoFit

         NewRwHt = c.RowHeight

        c.ColumnWidth = cWdth

      ma.MergeCells = True

     ma.RowHeight = NewRwHt

    cWdth = 0: MrgeWdth = 0

 Application.ScreenUpdating = True

 End If

 End With

 End Sub

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

4 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2020-03-28T20:11:02+00:00

    Awesome! Thank you again! It definitely works. I thought I was decent with excel. Turns out not so much. haha.. maybe with just the formulas I guess. Thank you again!

    Was this answer helpful?

    0 comments
  2. Anonymous
    2020-03-26T22:16:26+00:00

    re:  alternative to merge

    May or may not work for you...

    Format Cells dialog box | Alignment tab | Horizontal: drop down

    (both sentences are entered in column A; borders added for clarity)

    Was this answer helpful?

    0 comments
  3. Anonymous
    2020-03-26T20:10:10+00:00

    Well, shoot.Guess I can't post a picture. Thanks for the change. That worked! 

    As for merged cells, I've seen where lots of people have said not to merge. In the workbook we are using, there is a comments section  (column E-H) that the user will input maintenance log information. There are 4 cells in each row in that section that are merged.  I'm not sure how else to set up the sheet as the rows above it have information in each column A-H.  Hopefully that makes sense. I am definitely open to suggestions. :) Thanks again!!

    Was this answer helpful?

    0 comments
  4. Anonymous
    2020-03-26T19:27:22+00:00

    To:  Bekandbri

    re:  what to do with code

    You have Event code for a worksheet.  There is also Event code for the workbook.

    It is found in the ThisWorkbook module.

    It is...

       Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

      End sub

    You may be able to paste the contents of your Worksheet_SheetChange sub directly into the Workbook_SheetChange sub and have it work on all worksheets.

    Minor changes might be necessary.  I have not tested your code.

    '---

    Note: Advice, from many experienced Excel users, is "Don't merge cells".

    '---

    The free Excel workbook "Professional_Compare" compares every cell Or

    each row in two worksheets - choice of compare type.

    Includes "Clean Data, Quick Uniques, Descriptive Statistics, Benford Distribution" utilities.

    Download (no ads) from:  http://www.mediafire.com/folder/lto3hbhyq0hcf/Documents

    Was this answer helpful?

    0 comments