Workaround for Autofitting Rows and Merged Cells

Have you ever tried doing an autofit of a row by placing your mouse to the far left of the file, to the bottom of the row, and right clicking your mouse and nothing happens? It might be because you have merged cells in one of the columns.

But sometimes you need those merged cells. For example, I'm building an "automated" report that pulls in the requisite information for various sections (in my report it will be minimum wage, off duty behaviors, meals and breaks, etc.) Each section will have different number of columns and each column's width will be different. So column D for minimum wage may be different from column D in off duty behavior and different from column D in meals and breaks. I don't want the width of column D for minimum wage determining the width of column D in off duty behavior or meals and breaks, so I had to do merged cells to create the proper number of columns with the proper width.

But you can't autofit rows with merged cells.

The example file uploaded below gives you a rough idea of what I'm talking about. The file shows tabs "Template", "Hidden Template", "Autofit macro", "Off Duty Behavior", as well as other tabs. I'm just showing minimum wage and off duty behaviors here. The information for off duty behavior is totally bogus because I'm not sure where my company is pulling the information from (well, I think they may have bought it) and I just can't Google for the information.

I spent a week trying to figure out a way around the merged cells/autofit row conundrum. I spent a lot of the time Googling to see if anybody had figured it out but I had no luck.

I finally thought of a good workaround. The key is to have a tab that did not have merged columns but instead the columns are the same width as the merged cells. The "Hidden Template" is the tab for my autofit workaround. The "Hidden Template" pulls in the information you need. Column D pulls in Employer Restrictions from column H in "Template" tab. I estimated columns H through L to be about 70 in the "Template" tab so I made column D in the "Hidden Template" to be 70. Each "column" in the "Template" tab has a mirror in the "Hidden Template" tab.

See in column B, both in the "Template" and "Hidden Template" tabs the codes ods and ode? Those are my little codes to signify the start and end of off duty information (off duty start and off duty end). This is useful for macro purposes in case I start adding or deleting rows or moving things around.

Once I have this substitute tab set up, I can now set up a macro to autofit the substitute tab ("Hidden Template"), then collect row height information for each row in the substitute tab, and carry back this row height information to the main tab "Template".

Below is the macro which can also be found in the file too, in the tab "Autofit macro".

Macro

Sub AutofittingRows()
 
Dim ITEMS As Integer
Dim FindState As Variant
Dim FindRow As Long           'row # that FindState is located on
Dim FindHeight As Variant         'height for that row
Dim State As String
Dim StateInitial As String
 
'**************************************************************************************************************************************************
'Variables to denote on what rows the Off Duty information lies
Dim FindProp As Variant
Dim FirstRow As Integer
Dim LastRow As Integer
Dim FirstRowHid As Integer
Dim LastRowHid As Integer
 
Set FindProp = Sheets("Template").Range("B:B").Find(What:="ods", LookIn:=xlValues)
FirstRow = FindProp.Row
Set FindProp = Sheets("Template").Range("B:B").Find(What:="ode", LookIn:=xlValues)
LastRow = FindProp.Row
 
Set FindProp = Sheets("HiddenTemplate").Range("B:B").Find(What:="ods", LookIn:=xlValues)
FirstRowHid = FindProp.Row
Set FindProp = Sheets("HiddenTemplate").Range("B:B").Find(What:="ode", LookIn:=xlValues)
LastRowHid = FindProp.Row
 
'**************************************************************************************************************************************************
 
Sheets("Template").Activate
 

'Count number of items/rows with information. Some states have multiple rows of information.
TotalITEMS = Rows(FirstRow & ":" & LastRow).Count
BlankITEMS = WorksheetFunction.CountBlank(Range(Cells(FirstRow, 3), Cells(LastRow, 3)))
ITEMS = TotalITEMS - BlankITEMS
 

'Making sure the columns have wrap text on in order for autofitting to work properly
Sheets("Template").Range(Cells(FirstRow, 3), Cells(LastRow, 15)).WrapText = True     '(Columns C through O)
 

'Now go to my substitute tab "Hidden Template" and do autofitting. But first wrap text as a precaution
Sheets("HiddenTemplate").Activate
Sheets("HiddenTemplate").Range(Cells(FirstRowHid, 4), Cells(LastRowHid, 7)).WrapText = True
Range(FirstRowHid & ":" & LastRowHid).Select
Rows(FirstRowHid & ":" & LastRowHid).EntireRow.AutoFit

'Now, after autofitting, capture the row height of each row in the "Hidden Template" and apply the new row heights to the relevant rows in "Template"
For i = 1 To ITEMS
    With Sheets("HiddenTemplate")
        FindHeight = .Range(Cells(FirstRowHid - 1 + i, 4), Cells(FirstRowHid - 1 + i, 4)).RowHeight
    End With
    Sheets("Template").Rows(FirstRow - 1 + i).RowHeight = FindHeight
Next i
   
Sheets("Template").Activate
 
End Sub