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.
Additional Resources
The following tutorials explain how to perform other common tasks in VBA:
VBA: How to Count Number of Used Columns
VBA: How to Find Last Used Column
VBA: How to Delete Columns
