Adjust Row Height and Column Width with Excel VBA

When working with Excel, you may often find yourself thinking, 'I wish this column was a bit wider'," or "The row heights are all different and it's hard to read"? Manually adjusting each row or column can be tedious and time-consuming.

However, when dealing with large amounts of data or when you need to make the same adjustments across multiple sheets, there are limits to what can be achieved by doing things manually. How can you work more efficiently?

In fact, you can make these adjustments in an instant by using Excel macros (VBA). I ​​have been involved in Excel macro development for many years, and this method has saved many users a lot of time.

In this article, we will explain how to easily change row height and column width using Excel VBA.

Excel VBA may seem difficult at first glance, but by using the methods introduced in this article, you can easily harness the power of macros even if you have no programming experience.

For how to manually adjust the settings, please refer toHow to Adjust Column Width and Row Height in Excel (Manual Methods Explained).

How to Automatically Adjust Column Width and Row Height in Excel VBA

Let's examine a table where improper column widths and row heights affect data readability.

Because the dates in column "B" and the amounts in column "E" are too narrow, they are displayed as "#######" and the dates and amounts cannot be read.

Also, only the front part of column "C" is visible, and the back half is obscured by column "D" and cannot be read. Column "D" does not fit in the text and overlaps with column "E".

Auto-adjust column width

First, let’s adjust the width of columns “B to G” to display the cells that are too narrow to see.

To automatically adjust the width, use "EntireColumn.AutoFit".

The example is

Sub width_adjustment()
    Columns("B:G").EntireColumn.AutoFit
End Sub

If you run this

Columns "B~E" have been made wider, making the entered data easier to read.

On the other hand, rows "F" and "G" were too wide and the space wasted wasted, so they have been adjusted to a more appropriate size.

Auto-adjust row height

Next, we will adjust the row height of rows 2 to 12.

To automatically adjust the height, use "EntireRow.AutoFit".

The example is

Sub Height_adjustment()
    Rows("2:12").EntireRow.AutoFit
End Sub

If you run this

The "third row" has become wider. I didn't notice it at first, but the "D3" cell had a line break, and the word "stationery" was completely hidden. This macro automatically adjusted the height to display all the data.

Resize to any width and height

Change the column width to any size

Column "G" does not contain any data, so its width is set to the width of two characters for "Notes." However, since there is a possibility that multiple characters may be entered in the future, you would like to make it wider.

To specify the width to any size, use ".ColumnWidth = [any number]".

The example is

Sub width_adjustment()
    Columns("G:G").ColumnWidth = 30.05
End Sub

If you run this

We were able to increase the size of the notes.

You can change the size to your liking by changing the "30.05" part to your preferred number.

Change the row height to any size

Let's make the title line on the second line a little wider to make it easier to read.

To specify the height to any size, use ".RowHeight = [any number]".

The example is

Sub Height_adjustment()
    Rows("2:2").RowHeight = 30.05
End Sub

If you run this

The title line has been made a little wider to make it easier to read.

Conclusion

This time, we explained how to use macros to automatically adjust or change to any size.

You can also adjust it manually or change it all to the same size. For details, seeHow to Adjust Column Width and Row Height in Excel (Manual Methods Explained).