Auto adjust cell height (or row height) for adjacent merged cells

[Excelbuddy_7]

Hi

I have a table with 11 columns. I had to use merged cells in the table. Now I want to be able to auto-adjust the cell height (row height), with excel taking into account all the columns and set the cell height.

REQUIRED: Can excel take into account the data in the row, and auto adjust the cell height. It only needs to check the C, V, AP and CE columns in the pic (example table; columns alphabets are far apart because of merged cells). The columns in red are a dropdown list (for info).

CURRENT SITUATION: At the moment I have a code, which checks the cells required, but only in the particular order in the code. For example, first it checks C13, then it checks V13. (default cell height is so 1 line of text can be visible). Now when data in C13 is more than one line, it doesn't change the cell height because V13 (which has no data typed in yet) is only 1 line high (and it checks V13 after C13).

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim rChanged As Range, c As Range
  
  'Set a range equal to all the changed cells in the range (there could be more than one at a time)
  Set rChanged = Intersect(Target, Range("BD11:BP34"))
  
  'If something did change in the range then
  If Not rChanged Is Nothing Then
    
    'Disable events so when/if we change acell on the sheet this whole code doesn't get called again
    Application.EnableEvents = False
    
    'Work through each of the column D changed cells
    For Each c In rChanged
      
      'Add a "-" at the end of the cell value then split that result into bits by dividing it up at each "-"
      'This creates an array of 'bits', the first of which is referenced by zero (0)
      'Take that first 'bit', trim off any leading/trailing spaces & put that value in the cell
      c.Value = Trim(Split(c.Value & "-", "-")(0))
      
    'Repeat for other changed cells in the column
    Next c
    
    'Re-enable events ready for the next change to the worksheet
    Application.EnableEvents = True
  End If
  
  Dim mw As Single
Dim cM As Range
Dim rng As Range
Dim cw As Double
Dim rwht As Double
Dim ar As Variant
Dim i As Integer

Application.ScreenUpdating = False
'Cell Ranges below, change to suit.
ar = Array("C12", "C13", "V13", "C11")
For i = 1 To UBound(ar)
    On Error Resume Next
    Set rng = Range(Range(ar(i)).MergeArea.Address)
    With rng
      .MergeCells = False
      cw = .Cells(1).ColumnWidth
      mw = 0
      For Each cM In rng
          cM.WrapText = True
          mw = cM.ColumnWidth + mw
      Next
      mw = mw + rng.Cells.Count * 0.66
      .Cells(1).ColumnWidth = mw
      .EntireRow.AutoFit
      rwht = .RowHeight
      .Cells(1).ColumnWidth = cw
      .MergeCells = True
      .RowHeight = rwht
    End With
Next i
    Application.ScreenUpdating = True
End Sub

The first bit of code is from "http://www.mrexcel.com/forum/excel-questions/900007-replace-text-selected-drop-down-list-number-same-cell.html". (It is so the data from my drop down lists can be converted to just a number for future use).

The second bit of code (for adjusting cell height), was from another website.

Also at the moment, the first bit of code takes a long time to work as it is also adjusting the cell height after sorting out my dropdown lists. Is there any way to change this?

Another issue at the moment: In my array in the second code (C12, C13, V13, C11), for some reason the code doesn't work for the cell in the first position in the array (in this case C12).


Sorry if it is a bit confusing; hope it all makes sense. Please let me know if you need any more information.

Thanks for any help.

 

[Peter_SSs]

Sorry if it is a bit confusing;

It is ..

The first bit of code is from "http://www.mrexcel.com/forum/excel-questions/900007-replace-text-selected-drop-down-list-number-same-cell.html".

... the first bit of code takes a long time to work as it is also adjusting the cell height ...

The code from the other thread is mine, but it does nothing with row height so I can't reconcile the above two comments.



Merged cells are a pain, especially dealing with them in code!

In my array in the second code (C12, C13, V13, C11), for some reason the code doesn't work for the cell in the first position in the array (in this case C12)

That will be because the first element in your array will be ar(0) not ar(1)

Try changing this

<del>For i = 1 To UBound(ar)</del>
For i = LBound(ar) To UBound(ar)

 

[Excelbuddy_7]

It is ..


The code from the other thread is mine, but it does nothing with row height so I can't reconcile the above two comments.



Merged cells are a pain, especially dealing with them in code!




That will be because the first element in your array will be ar(0) not ar(1)

Try changing this

<del>For i = 1 To UBound(ar)</del>
For i = LBound(ar) To UBound(ar)

Thanks for your reply. I have moved on a bit more after this; realised the mistakes. But if possible, I could email my file to you and explain the situation?

 

[Peter_SSs]

But if possible, I could email my file to you and explain the situation?

That would defeat the board's purpose of being a public forum.

Related points are 18 & 19 of the Posting Guidelines and #7 of the Forum Rules, please review them.

 

[Excelbuddy_7]

That would defeat the board's purpose of being a public forum.

Related points are 18 & 19 of the Posting Guidelines and #7 of the Forum Rules, please review them.

OK, fair enough. I will explain it here.

I have a table with 11 columns. I had to use merged cells in the table. Now I want to be able to auto-adjust the cell height (row height), with excel taking into account all the columns and set the cell height.

REQUIRED: Can excel take into account the data in the row, and auto adjust the cell height. It only needs to check the C, V, AP and CE columns in the pic (example table; columns alphabets are far apart because of merged cells). The columns in red are a dropdown list (for info).

Just forget the previous codes.

Thanks

 

[Excelbuddy_7]

Also, I have found a code at the bottom of "Excel VBA, Dashboards, Charting, Financial modelling and more..." Look at "Fit Row Height to Largest Text". Just can't get it to work for me.

Hope this helps.

 

[Fishboy]

OK, fair enough. I will explain it here.

I have a table with 11 columns. I had to use merged cells in the table. Now I want to be able to auto-adjust the cell height (row height), with excel taking into account all the columns and set the cell height.

REQUIRED: Can excel take into account the data in the row, and auto adjust the cell height. It only needs to check the C, V, AP and CE columns in the pic (example table; columns alphabets are far apart because of merged cells). The columns in red are a dropdown list (for info).

Just forget the previous codes.

Thanks

I have a couple of questions...

Are any of the columns C, V, AP or CE the merged cells?
Are the values contained inside the result of formulas?
If I recall from your comments in this thread you wanted this to happen when you selected a specific cell. Which cell?

 

[Excelbuddy_7]

Are any of the columns C, V, AP or CE the merged cells?

Every single column in that table is a merged cell (had to do that because on top of the table I have some data, for which I needed a different column width. So I took 100 columns, each 10 pixels wide, and merged them, so I could get my own column width. Hope this bit makes sense). See attached picture.

This was initially a Word document, but I am converting it into an Excel document for ease of filling it up, without changing the formatting. Hence the columns widths are exact, and fit perfectly onto a page.

Are the values contained inside the result of formulas?

The values in columns BD, BH, BL, BP are from a drop down list. These lists are the ones I wanted in the other thread, where I wanted the list to show a number and data as well, but once the option is chosen I only wanted the number to come up. The value in column BT is a product of the preceding 4 columns. The BZ column is just a simple-straightforward dropdown list (the list options are just 3 alphabets: A; B; or C)


Hope this helps. Thanks

 

[Fishboy]

OK, and which cells are the ones you foresee as needing to have their heights adjusted? I mean, which cells are likely to be so full of information that the row needs to made taller as a result of their contents?

 

[Excelbuddy_7]

OK, and which cells are the ones you foresee as needing to have their heights adjusted? I mean, which cells are likely to be so full of information that the row needs to made taller as a result of their contents?

C, V, AP, CE

Thanks