[VBA] How to Automatically Adjust Row Height and Column Width

Overview

When working in Excel, the width of columns or the height of rows often does not match the input data, causing text to be cut off. Manually adjusting these one by one can be very time-consuming, especially when dealing with a large amount of data.

In this article, I will introduce a method using VBA to automatically adjust rows and columns to fit your data.

Sample Code

Sub AdjustRowAndColumn()
    With Range("B2").CurrentRegion
        .EntireRow.AutoFit
        .EntireColumn.AutoFit
    End With
End Sub

Code Explanation

• Range("B2").CurrentRegion This selects the continuous range of data that includes cell B2. By using CurrentRegion, the code automatically recognizes the target area even if the amount of data increases or decreases, so you do not need to change the range manually.
• .EntireRow.AutoFit This automatically adjusts the row height. The height is determined by the largest text or content within the cell.
• .EntireColumn.AutoFit This automatically adjusts the column width. The width is set to the optimal size based on the cell contents.

Use Cases

• Automatically formatting a report after it is generated.
• Instantly optimizing column widths after importing data.
• Formatting a sheet before printing to ensure everything is visible.

Summary

By using AutoFit, you can adjust Excel row heights and column widths all at once to match your data. This significantly improves readability and efficiency, especially when handling large datasets or external data imports.