[Excel VBA] How to Shrink Cell Contents to Fit | Using the ShrinkToFit Property

2025年12月28日

Overview

In Excel, when the text entered in a cell is too long, it often overflows the cell width or gets cut off. In such cases, instead of manually adjusting the column width, you can automatically shrink the font size to fit the text inside the cell.

In this article, I will explain how to “Shrink to fit” using VBA with practical code examples.

Sample Code

Sub FitTextToCell()

    ' Shrink the contents of the specified cell to display the entire text
    Range("D4").ShrinkToFit = True

End Sub

Explanation of the Code

Range(“D4”)This is the target cell to apply the shrink-to-fit setting. You can also specify a range of cells (e.g., Range(“D4:F6”)) if needed.
.ShrinkToFit = TrueIf the content of the cell does not fit within the column width, the font size is automatically reduced so that the entire text is displayed. The text size adjusts dynamically so that the content is not hidden.

Important Notes on Usage

Row Height: ShrinkToFit does not automatically adjust the vertical height of the cell (row height). The text is shrunk while remaining on a single line.
Conflict with WrapText: This property generally cannot be used in conjunction with “Wrap Text” (WrapText). If both are enabled, the display may not behave as intended (usually Wrap Text takes precedence or the shrink effect is nullified).
Batch Application: When applying to multiple cells, you can specify a range like Range("D4:F6").ShrinkToFit = True.

Application Examples

Fixed Layouts: When you want to prevent text cutoff in input forms where column widths are fixed.
Reports: When you want to keep all values inside cells to maintain a neat appearance for printing.
User Input: When you want to maintain readability in user input fields while preserving the overall design.

Other Related Properties

Property Name	Description
`.WrapText`	Wraps text within the cell to display it on multiple lines.
`.MergeCells`	Controls the merged state of cells.
`.Orientation`	Changes the angle of the text (e.g., vertical text).
`.Font.Size`	Directly specifies the font size.

Summary

In this article, I introduced how to use VBA to shrink cell contents so that the entire text is visible. By using the .ShrinkToFit property, you can ensure the readability of the content while keeping column widths fixed, preventing layout issues.

Please make use of this when you want to fine-tune the appearance of your Excel sheets or print layouts.

よかったらシェアしてね！