Wednesday, July 6, 2016

Auto-sizing of merged cells with wrapped text.

Auto-sizing of merged cells with wrapped text.

How do I auto-size a row height so that the text in a set of merged cells containing wrapped text will automatically expand vertically to the required height to display the text?
I have tried Format/Autofit to row height and double-clicking on the bottom margin of the row in the row number column.  Both result in the row resizing to a single line height.  Thus far I am only able to get all of the text to show if I manually drag the
bottom row margin the required distance so that all of the text displays, then save the workbook.
This feature worked in 2003; how does it work in 2007?

Solutions to the Problem Auto-sizing of merged cells with wrapped text.

Download SmartPCFixer to Fix It (Free)

I don't think it did work in 2003.
There has always been a problem with wrapped merged cells and autofit.
I have written a macro to do the row height setting when there are wrapped merged cells:

Sub test()
  SetRowHeights ThisWorkbook.Sheets("Sheet1")
End Sub
Sub SetRowHeights(Sh As Object)
' sets row heights in sheet Sh.
' Excel doesn't correctly set row height when merged cells have wrapped text
  Dim C As Range, rRow As Range
  Dim sHeight As Single
  Dim sBestHeight As Single
  Dim bUpdate As Boolean
  Dim bHid As Boolean
  Dim iHidCol As Integer
  Dim cSizer As Range
 
  ' switch off screen updating to speed up the process
  bUpdate = Application.ScreenUpdating
  Application.ScreenUpdating = False
 
 
  ' this process is only relevant to worksheets, not chart sheets
  If TypeName(Sh) = "Worksheet" Then
    If IsNull(Sh.UsedRange.WrapText) Or Sh.UsedRange.WrapText Then
      ' text wrapping done in some cells in the sheet
      Workbooks.Add xlWorksheet ' temporary workbook
      Set cSizer = Range("A1")  ' a cell to use as workspace
     
      For Each rRow In Sh.UsedRange.Rows
        If IsNull(rRow.WrapText) Or rRow.WrapText Then
          ' there are cells on this row with wrapped text
          If Not IsNull(rRow.MergeCells) Then
            ' no merged cells so can use Excel's autofit
            rRow.EntireRow.AutoFit
          Else
            ' row has merged cells and wrapped text
            sBestHeight = 12.75
            For Each C In rRow.Cells
              ' copy the content of the cell to a spare cell in Terms and Autofit there
              If C.Address = C.MergeArea.Range("A1").Address _
                   And C.WrapText And Not C.EntireColumn.Hidden Then
                ' first of a merged cell, or a single cell, with wrapped text
                ' and column not hidden
                ' set the single cell in Terms to match the (merged) cell here
                cSizer.Value = C.Text
                cSizer.Font.Size = C.Font.Size
                cSizer.Font.Bold = C.Font.Bold
                ' Width is measured in Twips and we can find the width of the MergeArea
                ' but we can only set the ColumnWidth which is measured in different units
                ' so scale the Width appropriately
                cSizer.EntireColumn.ColumnWidth = C.MergeArea.Width * cSizer.ColumnWidth / cSizer.Width
                cSizer.WrapText = True
                ' use AutoFit to find the right row height for this cell
                cSizer.EntireRow.AutoFit
                ' get the height
                sHeight = cSizer.RowHeight
                ' if the cell is merged vertically then we need less height than this
                If C.MergeArea.Rows.Count > 1 Then
                  ' adjust height down for later rows
                  sHeight = sHeight - (C.MergeArea.Rows.Count - 1) * (C.Font.Size + 2.75)
                End If
              Else
                sHeight = C.Font.Size + 2.75
              End If
              ' take the greatest height for this row so far
              If sHeight > sBestHeight Then sBestHeight = sHeight
            Next
            ' if the row isn't the correct height
            If rRow.EntireRow.RowHeight <> sBestHeight Then
              ' set it to the correct height
              rRow.EntireRow.RowHeight = sBestHeight
            End If
          End If
        End If
      Next
      ' close the helper workbook
      ActiveWorkbook.Close False
    End If
  End If
  ' restore screenupdating to its previous state
  Application.ScreenUpdating = bUpdate
End Sub

Bill Manville.
Excel MVP, Oxford, England.
www.manville.org.uk

Don't Download Malware

Where are you getting the download?

There are malicious people who download valid copies of a popular download, modify the file with malicious software, and then upload the file with the same name. Make sure you are downloading from the developer's web page or a reputable company.


Cancel or deny any automatic download

Some sites may automatically try start a download or give the appearance that something needs to be installed or updated before the site or video can be seen. Never accept or install anything from any site unless you know what is downloading.

Avoid advertisements on download pages

To help make money and pay for the bandwidth costs of supplying free the software, the final download page may have ads. Watch out for anything that looks like advertisements on the download page. Many advertisers try to trick viewers into clicking an ad with phrases like "Download Now", "Start Download", or "Continue" and that ad may open a separate download.

Recommended Method to Repair the Problem: Auto-sizing of merged cells with wrapped text.:

How to Fix Auto-sizing of merged cells with wrapped text. with SmartPCFixer?

1. Click the button to download Error Fixer . Install it on your system.  Run it, and it will scan your computer. The junk files will be shown in the list.

2. After the scan is finished, you can see the errors and problems need to be repaired. Click Fix All.

3. The Repair part is finished, the speed of your computer will be much higher than before and the errors have been fixed. You can also use other functions in this software. Like dll downloading, junk file cleaning and print spooler error repair.


Related: AMD Radeon HD 7800M Win8 not working [Anwsered],I can access the internet, get on facebook and get to hotmail, but I can't play games on facebook and I can't open or respond to my e-mails,I keep getting this Media Player error when I log on my computer. [Anwsered],[Anwsered] System Hanging on shutdown and restart,Unable to get the Vlookup property of the WorksheetFunction class,Solution to Error: Error: "0x81000032 make sure the C: drive is online and set to NTFS" when trying to backup to external hard drive.
,Troubleshoot:External Hard Drive not listed in Windows 7 backup wizard Error
,I'm always being signed off so annoying Tech Support
,Solution to Problem: Impossible to use Internet Explorer! I keep getting the same error message every time i try to use IE.
,Solution to Problem: Referencing data in another file
,Troubleshoot:Error: "0x81000032 make sure the C: drive is online and set to NTFS" when trying to backup to external hard drive. Error,External Hard Drive not listed in Windows 7 backup wizard Tech Support,Tech Support: I'm always being signed off so annoying,Solution to Problem: Impossible to use Internet Explorer! I keep getting the same error message every time i try to use IE.,Referencing data in Access using Excel [Anwsered],Need Best Way To Present Data [Anwsered],Same question but for windows 7 home edition,sometimes fullscreen won't activate [Solved],Solution to Error: We bought a new computer with windows 7 and it is constantly freezing. How do we fix this?,Solution to Error: Windows 8 update crash (2013-07-22)
Read More: [Anwsered] back up stops and asks me to insert a media that is labeled,Automatic update of file location in the header\/footer? [Anwsered],Troubleshooting:ArchiveFileForUninstall:ArchiveSingleFile Canceled Error,Troubleshoot:Backup has stopped working. Error,How to Fix Error - AverageIF function to include any numbers more than 0, so long as a different column has a certain value in it?,application not found error,any problems in a team where one has Windows XP and the other has Windows 7?,Application/Object-Defined Error,An Excel formula question where hours are totalled and cumulating,Anyone know the hardware email?

No comments:

Post a Comment