[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Excel - Autofit Row Height

Posted on 2014-08-20
9
Medium Priority
?
294 Views
Last Modified: 2014-09-29
I use ALT+Enter to add another text line within the same cell.  Some single cells have up to 4 to 6 lines.  Now I want to use Autofit by double clicking the row line to Autofit the row height. However, when I do this the row adjusts to only show the top line of text in the cell and not all 4-6 lines.  Is there a way to Autofit and show all lines in the cell?
0
Comment
Question by:Hank11
  • 4
  • 4
9 Comments
 
LVL 54

Assisted Solution

by:Rgonzo1971
Rgonzo1971 earned 668 total points
ID: 40275454
Hi,

is the cell with Wrap Text ticked?

Regards
0
 

Author Comment

by:Hank11
ID: 40275532
same thing happens whether the cell has wrap text turned on or off.
0
 
LVL 54

Expert Comment

by:Rgonzo1971
ID: 40275600
Could you send a dummy?
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 

Author Comment

by:Hank11
ID: 40275636
0
 
LVL 54

Expert Comment

by:Rgonzo1971
ID: 40275647
Now I see

It cannot autofit because of the merged Cells

Regards
0
 

Author Comment

by:Hank11
ID: 40275686
So how do I Autofit multiple lines?
0
 
LVL 54

Accepted Solution

by:
Rgonzo1971 earned 668 total points
ID: 40275791
You could use this macro

select the merged cell to be adjusted

Sub AdjustHeightMergedCell()

MrgAddr = ActiveCell.MergeArea.Address
Set rngMergeArea = Range(MrgAddr)
With rngMergeArea
    .UnMerge
    cellWidth = .Cells(1).ColumnWidth
    MrgWidth = 0
    For Each c In rngMergeArea
        c.WrapText = True
        MrgWidth = c.ColumnWidth + MrgWidth
    Next
    .Cells(1).ColumnWidth = MrgWidth
    .EntireRow.AutoFit
    rwHeight = .RowHeight
    .Cells(1).ColumnWidth = cellWidth
    .MergeCells = True
    .RowHeight = rwHeight
End With

End Sub

Open in new window

Regards
0
 

Author Comment

by:Hank11
ID: 40278053
I appreciate your help, but I can simply adjust and drag the row line underneath.  I was looking for a way to double click a row line and all lines would AutoFit height.
0
 
LVL 24

Assisted Solution

by:Ejgil Hedegaard
Ejgil Hedegaard earned 332 total points
ID: 40283956
Try this, with Rgonzo1971 code expanded to adjust all merged cells on the sheet.
It looks a bit complex for that, but it is to only adjust once for each area of merged cells.

Option Explicit

Sub AdjustHeightMergedCell()
Dim MrgAddr As String, rngMergeArea As Range, c As Range, cellWidth As Double, MrgWidth As Double, rwHeight As Double
Dim ws As Worksheet, cl As Range, MergeList() As Variant, i As Long, j As Long, k As Long, l As Integer

    Application.ScreenUpdating = False
    Set ws = ActiveSheet
    ws.Rows.AutoFit
    i = 0
    For Each cl In ws.Range(Cells(1, 1), Cells(ws.Cells.SpecialCells(xlCellTypeLastCell).Row, ws.Cells.SpecialCells(xlCellTypeLastCell).Column))
        If cl.MergeArea.Address <> cl.Address Then
            i = i + 1
        End If
    Next cl
    ReDim MergeList(1 To i, 1 To 2)
    i = 0
    For Each cl In ws.Range(Cells(1, 1), Cells(ws.Cells.SpecialCells(xlCellTypeLastCell).Row, ws.Cells.SpecialCells(xlCellTypeLastCell).Column))
        If cl.MergeArea.Address <> cl.Address Then
            i = i + 1
            If i = 1 Then
                MergeList(i, 1) = cl.Address
                MergeList(i, 2) = cl.MergeArea.Address
                j = 1
            Else
                l = 0
                For k = 1 To j
                    If MergeList(k, 2) = cl.MergeArea.Address Then
                        l = 1
                    End If
                Next k
                If l = 0 Then
                    j = j + 1
                    MergeList(j, 1) = cl.Address
                    MergeList(j, 2) = cl.MergeArea.Address
                End If
            End If
        End If
    Next cl
    For i = 1 To j
        MrgAddr = MergeList(i, 2)
        Set rngMergeArea = Range(MrgAddr)
        With rngMergeArea
            .UnMerge
            cellWidth = .Cells(1).ColumnWidth
            MrgWidth = 0
            For Each c In rngMergeArea
                c.WrapText = True
                MrgWidth = c.ColumnWidth + MrgWidth
            Next
            .Cells(1).ColumnWidth = MrgWidth
            .EntireRow.AutoFit
            rwHeight = .RowHeight
            .Cells(1).ColumnWidth = cellWidth
            .MergeCells = True
            .RowHeight = rwHeight
        End With
    Next i
End Sub

Open in new window

0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

872 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question