Jump to content


 


Register a free account to unlock additional features at BleepingComputer.com
Welcome to BleepingComputer, a free community where people like yourself come together to discuss and learn how to use their computers. Using the site is easy and fun. As a guest, you can browse and view the various discussions in the forums, but can not create a new topic or reply to an existing one unless you are logged in. Other benefits of registering an account are subscribing to topics and forums, creating a blog, and having no ads shown anywhere on the site.


Click here to Register a free account now! or read our Welcome Guide to learn how to use this site.

Photo

Problem with routine to auto-adjust row height for merged cells in Excel


  • Please log in to reply
No replies to this topic

#1 adaniel

adaniel

  • Members
  • 203 posts
  • OFFLINE
  •  
  • Local time:01:48 PM

Posted 05 June 2013 - 01:51 PM

Windows XP

Excel 2003

 

I am new to VB Macros in Excel, but have had little trouble finding what I need and altering macros to suit my specific needs.  I have run into one now, however, I cannot get to work.

 

I have an invoice form with a variable length description.  The descriptions are in cells d7 through d34.  My problem is that D7 through F7 are merged.  When I enter a description that is longer than will fit on one row, it does not expand the row height.  I found that to be a known problem in excel and found a macro to do what I need.

 

What I am trying to accomplish is to enter the description in D7.  Upon pressing TAB or ENTER, I want the row to expand in height to display all the text.

 

Here is the code I am using. 

 

Private Sub WorksheetChange(ByVal Target As Range)
    Select Case Target.Address
    Case "$d$17", "$D$18"
        Call AutoFitMergedCellRowHeight
    End Select
End Sub

 

 

Sub AutoFitMergedCellRowHeight()

Dim CurrentRowHeight As Single, MergedCellRgWidth As Single
Dim CurrCell As Range
Dim ActiveCellWidth As Single, PossNewRowHeight As Single
If ActiveCell.MergeCells Then
    With ActiveCell.MergeArea
        If .Rows.Count = 1 And .WrapText = True Then
            Application.ScreenUpdating = False
            CurrentRowHeight = .RowHeight
            ActiveCellWidth = ActiveCell.ColumnWidth
            For Each CurrCell In Selection
                MergedCellRgWidth = CurrCell.ColumnWidth + _
                MergedCellRgWidth
            Next
            .MergeCells = False
            .Cells(1).ColumnWidth = MergedCellRgWidth
            .EntireRow.AutoFit
            PossNewRowHeight = .RowHeight
            .Cells(1).ColumnWidth = ActiveCellWidth
            .MergeCells = True
            .RowHeight = IIf(CurrentRowHeight > PossNewRowHeight, _
            CurrentRowHeight, PossNewRowHeight)
        End If
    End With
End If

End Sub

 

 

 

 

I get no errors, but it does not work.  I entered the code as follows:

  • Right click on spreadsheet tab
  • Click View Code
  • Double click on Sheet1 
  • Paste and modify code ( all I changed was the range)
  • Click File --> Close and return to Excel
  • Save

When I exit the description cell D7, it remains one row high regardless of the length of the description.

 

Thanks in advance for any assistance.

 

adaniel



BC AdBot (Login to Remove)

 





0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users