Table of Contents
Changing column width in VBA is a simple process which involves the use of the Columns.ColumnWidth property. An example of how to use this property to change the column width of column A to 20 is as follows: Columns(“A”).ColumnWidth = 20. This command will resize column A so that each cell is 20 characters wide. To change the width of multiple columns in one go, use a For Loop to iterate through the columns and assign the desired widths using the Columns.ColumnWidth property.
You can use the following methods to change the width of columns in Excel using VBA:
Method 1: Change Width of One Column
Sub ChangeColumnWidth()
Columns("B").ColumnWidth = 20
End Sub
This particular macro changes the width of column B to 20.
Note: The default width of columns in Excel is 8.29.
Method 2: Change Width of Multiple Columns
Sub ChangeColumnWidth()
Columns("B:D").ColumnWidth = 20
End Sub
This particular macro changes the width of all columns in the range from B to D to 20.
Method 3: Auto Adjust Width of Multiple Columns
Sub ChangeColumnWidth()
Columns("B:D").AutoFit
End Sub
This particular macro automatically adjusts the width of each column in the range from B to D to be as wide as necessary to display the longest cell in each column.
The following examples show how to use each of these methods in practice with the following dataset in Excel:
Example 1: Change Width of One Column
We can create the following macro to change the width of column B to 20:
Sub ChangeColumnWidth()
Columns("B").ColumnWidth = 20
End Sub
When we run this macro, we receive the following output:
Notice that only the width of column B (the “Points” column) has increased to 20 while the width of all other columns remained the same.
Example 2: Change Width of Multiple Columns
We can create the following macro to change the width of columns B through D to 20:
Sub ChangeColumnWidth()
Columns("B:D").ColumnWidth = 20
End Sub
When we run this macro, we receive the following output:
Notice that the width of each column from B to D has increased to 20 while the width of column A remained the same.
Example 3: Auto Adjust Width of Multiple Columns
We can create the following macro to automatically adjust the width of each column from A to D to be as wide as necessary to display the longest cell in each column.
Sub ChangeColumnWidth()
Columns("A:D").AutoFit
End Sub
When we run this macro, we receive the following output:
Notice that the width of each column has automatically been adjusted to be as wide as necessary to display the longest cell in each column.