View Full Version : VBA code for Autofit row height for rows with merged and unmerged/normal cells

12-27-2017, 04:05 AM
Hi Everyone,
I have a worksheet (ranges A7 to E30) which contains a mix of merged and un-merged cells in each row.. What I would like to do is to create a button in which it will autofit the size of the rows based on the highest row size for both merged and un-merged... I have already prepared the script that will autofit the row size if it's a merged cell, however, I'm having a challenge when it comes to unmerged cells.. If the current row height is currently greater than the autofit height of unmerged cells (because of the merged cell autofit row height), I want to leave it as is... However, if it is less, then I want to use the autofit function to adjust to the correct row size..

Here's my working code so far but currently got stuck..

Sub TestForMergedCell_version2()Dim rCheckCell As Range
Dim rCheck As Range
Dim HeightChecker As Integer
On Error Resume Next
Application.ScreenUpdating = False 'Speed up code and stop screen flickering
Application.EnableEvents = False 'Also stops endless loops in Events
Application.DisplayAlerts = False

Set rCheck = ActiveWorkbook.ActiveSheet.Range("A7:F90")
Set rCheckCell = ActiveCell
For Each rCheckCell In rCheck.Cells
If rCheckCell.MergeCells = True Then
With rCheckCell
.RowHeight = 1
.WrapText = True
h = .Cells(1).RowHeight
With .Cells(1).MergeArea
.Cells(.Cells.Count).RowHeight = _
.Cells(.Cells.Count).RowHeight + (h - .Height)
End With
End With
End If
If rCheckCell.MergeCells = False Then
With rCheckCell
HeightChecker = 30
If rCheckCell.RowHeight < HeightChecker Then rCheckCell.WrapText = True
End With
End If
End Sub


12-27-2017, 10:25 AM
Merged cells and VBA really don't mix! However I maybe able to help. First thing to point out is that during the Christmas break I've only access to Excel version 2003, so this may not apply to you.
For example, I had more difficulty autofitting merged cells then unmerged cells, which is NOT what you report. This is what I found:

Autofit of rows didn't work for merged cells (row 24 of your sheet) butů

Unmerge the cells
Format the cells: Alignment tab, Horizontal: Centre across selection, OK.
Then Autofit the row and the corrrect height is obtained. Make a note of this height.
Re-merge your cells (it autofits automatically after doing that here), then set the height to the height you made a note of before.

In code, something like:
With Range("D24:E24")
.MergeCells = False
.HorizontalAlignment = xlCenterAcrossSelection
x = .Height 'make a note of the height
.MergeCells = True 'remerge your cells (which adjusts the height too here)
.RowHeight = x 'set the height
End With
This is just pointer code; you'll have to determine what cells are merged and so on.

I'll have access to a more up-to-date version of Excel in a few days' time, so if the above doesn't apply, say so and I'll be in a better position to advise.
What would be a lot cleaner, is not to use merged cells at all; could you work with Centre Across Selection (it looks very, very similar)? I think you might find the coding only requires one line (entirerow.autofit) for the entire range!

Now on to a bit of an aside, and likely more important for you.
This is your first post here and it's clear you haven't read the rules in full, because you have cross-posted this question in more than one forum but haven't told everyone that you have done so (that is, you haven't supplied links). It is very exasperating for someone helping you, to find that the significant time they've spent giving you a soution has been utterly wasted because later, they find that you already had been given a good solution elsewhere.
There are fewer helpers than those seeking help, and those helpers often help on multiple forums, so news gets around. So any person requesting help who, despite being informed, continues to cross post their questions without supplying links soon finds out that people are much less willing to help. (I for one, never help repeat offenders again - quite simply because I don't want to go hunting to see if the person's already got an answer elsewhere.)
Cross posting is generally OK, not supplying links is definitely not OK. This applies to 9 out of 10 forums. It's your duty after all, to read the rules before you post.
You need to supply links, still, for this thread.
The same applies at mrExcel. Supplying links will help you in an other way too; others will be able to see what solutions have been offered and see what progress is being made and offer relevant advice.
[I don't normally ramble on like this, but today, I have the time!]
Take a look at http://www.excelguru.ca/content.php?184-A-message-to-forum-cross-posters