Home → Wiki → Ops chart export is not sizing row height correctly → Ops Chart: Export - Row Height

25.1. Ops Chart: Export - Row Height

a] Open Excel
b] Press Alt-F11 to open the Visual Basic editor
c] Press Ctrl-G to get the immediate (debugging) window
d] Type '?application.StartupPath' and press enter. You will get the Excel startup path for example I got:
"C:\Documents and Settings\User\Application Data\Microsoft\Excel\XLSTART"
e] Close the Visual Basic editor and create a new Workbook / XLS file
f] Save this file as "Personal.xls" (older versions of Excel) or a Macro-Enabled "Personal.xlsx" (for newer versions of Excel) in the directory as found in d]
g] Press Alt-F11 to open the Visual Basic editor
h] Double click "ThisWorkbook" on the top left to open the code editor for the workbook
i] Paste the code:

''Simulates row height autofit for a merged cell if the active cell..
'' is merged.
'' has Wrap Text set.
'' includes only 1 row.
''Unlike real autosizing the macro only increases row height
'' (if needed). It does not reduce row height because another
'' merged cell on the same row may needed a greater height
'' than the active cell.
Sub AutoFitMergedCellRowHeight()
Dim CurrentRowHeight As Single, MergedCellRgWidth As Single
Dim CurrCell As Range
Dim ActiveCellWidth As Single, PossNewRowHeight As Single

Dim StartSel As Range
Dim SelCell As Range

Set StartSel = Selection

For Each SelCell In StartSel
If SelCell.MergeCells Then
With SelCell.MergeArea
.Select
If .Rows.Count = 1 And .WrapText = True Then
Application.ScreenUpdating = False
CurrentRowHeight = .RowHeight
ActiveCellWidth = SelCell.ColumnWidth
MergedCellRgWidth = 0
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
Next SelCell

StartSel.Select

End Sub

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

j] Close the Visual Basic window

k] Go to Tools->Macro->Macros / [View > Macros > View Macros] <--- Excel 2010 and above

l] Click on "ThisWorkbook.AutoFitMergedCellRowHeight" and then Options. Choose a shortcut key, I chose 'r' for "resize" (you don't need to press the ctrl-key, just the letter)

m] Click Ok, close the macro window and save the Personal.xls spreadsheet/ [Save as Excel Macro-enabled workbook under Files of Type] <--- Excel 2010 and above

n] Open an Ops Chart export, navigate to the notes worksheet, select all the notes and press Ctrl-R

This page was: Helpful | Not Helpful