Excel VBA to Set Column Width (Customizing with 6 Ways)

In this article, we will learn how to use Excel VBA to set the column width. The width of a column in an Excel Sheet is a crucial factor in displaying the dataset eloquently. So, users need to set the column width properly. They can manually set the column width. However, if the Excel Workbook has multiple Sheets and they have to set the column width in each Sheet, then it becomes time-consuming as well as monotonous. In this case, VBA codes can reduce the burden on users by setting the column width of Sheets quickly just by running the code.

The below video illustrates vividly the use of VBA to customize the column width. Here, the B column is clearly not wide enough to make the dataset clearly visible. By running a VBA code, the width of the column is reset to properly display the dataset.

How to Launch VBA Macro Editor in Excel

In order to run any VBA code, we first need to write or edit the code in the VBA Macro Editor.

Before that, we need to enable the Developer tab, which remains hidden by default. The Developer tab allows users to access the VBA Macro Editor. Do the following tasks.

• Go to Developer tab >> Visual Basic.
• This will open the Visual Basic window.

• Select Insert >> Module in the macro editor.

• As a result, an empty module will appear on the screen where you can write the code. However, use the Run icon or F5 key to run the code.

Excel VBA to Set Column Width: 6 Handy Ways to Customize

Today we are going to discuss how to set column width in five handy ways. Here, we have a dataset containing the datasets of some books with their authors’ names, genres, and publication years. We will set the column width of the dataset using VBA codes so that the dataset can be seen properly.

1. Use Range.ColumnWidth Property

Users can set the column width according to their needs by using the Range.ColumnWidth property. Users only have to mention the cell, range, or column name and the width that they want to set for that cell, range, or column; this property will automatically update the width.

1.1 Setting Column Width for Single Column

In this example, we will set the width of a single column. As the image below suggests, the “Books” column width is not enough to hold the content of the column. So, we will reset the width of the column by using the Range.ColumnWidth property.

The following code sets the width of a single column.

Sub Single_ColumnWidth()
Worksheets("Single_Column").Range("B4").ColumnWidth = 30
End Sub

The code selects the “Single_Column” Worksheet. Then, it selects the B4 range, and with the .ColumnWidth property sets the column width to 30.

As we run the code, the width of the B column will be set to 30.

1.2 Setting Column Width for Multiple Contiguous (Adjoining) Columns

In this case, we will set the column width of multiple adjacent columns using VBA code. Here, we will reset the width of the B:D columns.

The code below will set the column width of the B:D columns to 30.

Sub Multiple_ColumnsWidth()
Worksheets("Multiple_Columns").Range("B:D").ColumnWidth = 30
End Sub

The code sets the width of the B:D columns of the “Multiple_Columns” Sheet to 30.

As we can see from the image below, the width of the B:D columns has been reset to 30, which is now wide enough to hold the dataset properly.

1.3 Setting Column Width for Non-Contiguous (Discrete) Columns

As we can see from the image below, the width of the B, D, and G columns is not enough to display the dataset properly. We will run a VBA code to set the width of these discrete columns.

The code below sets the B, D, and G columns’ width to 30.

Sub Discrete_ColumnsWidth()
Worksheets("Discrete_Columns").Range("B:B,D:D, G:G").ColumnWidth = 30
End Sub

The code above uses the Worksheet.Range.ColumnWidth property to set the width of the B, D, and G columns of the “Discrete_Columns” Worksheet to 30.

As we can see in the image below, the B, D, and G columns are now wide enough to hold the data inside these columns.

2. Apply ColumnWidth Property with Columns Property to Set Column Width

In this case, the column width of the “Authors” column is not enough to display the data inside the columns properly. We will use the Worksheets.Columns.ColumnsWidth property to reset the columns’ width.

The following code will apply the Worksheets.Columns.ColumnWidth property to set the column width.

Sub Columns_Property()
Worksheets("Columns_Property").Columns("B").ColumnWidth = 30
End Sub

The code will set the column width of the C column in the “Column_Property” Worksheet to 30.

As soon as we run the code above, the width of the C column will be reset and the data inside the column will be properly visible.

3. Use Range.AutoFit Property to Auto Fit Column Width

The Range.AutoFit property does not require any width value. It simply adjusts the width of the column so that the dataset is clearly visible.

3.1 Setting Column Width Based on Entire Column

In this case, the names of the authors in the “Authors” column are not clearly visible. The width of the column is not enough to make them visible. Here, we will change the width of the column so that it automatically fits the data.

The following code adjusts the width of the entire C column to fit the data inside it.

Sub Autofit_EntireColumn()
Worksheets("Autofit_EntireColumn").Range("C4").EntireColumn.AutoFit
End Sub

The VBA code above automatically adjusts the width of the entire C column of the “Autofit_EntireColumn” Worksheet to fit the content of the column.

Now, as we can see, the code fits the authors’ names perfectly inside the column.

3.2 Setting Column Width Based on Single Cell

Here, we can see that the B11 cell is not wide enough to make the data inside it clearly visible. In this case, we will use a VBA code that will change the width of the B11 cell so that the data can be seen clearly. In the process, the width of the entire B column will be changed accordingly.

The code below will change the width of the B11 cell automatically.

Sub Autofit_SingleCell()
Worksheets("Autofit_SingleCell").Range("B11").Columns.AutoFit
End Sub

The code takes the value of the B11 cell and then changes the width of the entire column so that the B11 cell can display the data clearly.

Read More: Excel VBA to Set Column Width Based on Cell Value

4. Set Column Width in Points

The measurement unit for row heights in Excel points is equivalent to 1/72 of an inch. However, for column widths, the unit of measurement is based on the number of zeros that can fit within the column. The zeros used for this purpose are of the Normal style, irrespective of the font or style of the selected range. For instance, if the Normal style of the worksheet is Arial 10, then the default width for a column would be 8.43, indicating that 8.43 zeros can fit within a cell.

The code below will set the column width of the B column to 200.

Sub ColumnsWidth_inPoint()
Dim ws As Worksheet
Set ws = Worksheets("ColumnsWidth_inPoint")
With ws.Range("B:B")
    For iCol = 1 To 3
        .ColumnWidth = 200 * (.ColumnWidth / .Width)
    Next iCol
End With
End Sub

Now, the “Books” column width is set to 200 points.

5. Set Column Width in Centimeters

As we can see in the image below, the “Books” column is not wide enough to display the dataset inside the column clearly. Here, we will change the width of the column in centimeters so that the column is wide enough to make the dataset clearly visible.

The following code will set the width of the B column in centimeters.

Sub ColumnsWidth_inCentimeters()
Dim ws As Worksheet
Set ws = Worksheets("ColumnsWidth_inCentimeters")
With ws.Range("B:B")
    For iCol = 1 To 3
        .ColumnWidth = Application.CentimetersToPoints(6) * (.ColumnWidth / .Width)
    Next iCol
End With
End Sub

This code is almost the same as the previous code. Here too, we will iterate the targeted width of the B column three times to make the width as close as possible to the targeted width. However, unlike the previous code, here is the Application.CentimetersToPoints(6) line converts 6 centimeters to points first and then sets the column width of the B column.

Now, the “Books” column is wide enough to display the data inside it clearly.

6. Set Column Width in Inches

Here, the “Books” column of the dataset is not wide enough to display it clearly. We will change the width of the column in inches to make the dataset clearly visible.

The following code is going to set the width of the B column to 2.5 inches.

Sub ColumnsWidth_inInches()
Dim ws As Worksheet
Set ws = Worksheets("ColumnsWidth_inInches")
With ws.Range("B:B")
    For iCol = 1 To 3
        .ColumnWidth = Application.InchesToPoints(2.5) * (.ColumnWidth / .Width)
    Next iCol
End With
End Sub

This code is almost the same as the previous code. Here too, we will iterate the targeted width of the B column 3 times to make the width as close as possible to the targeted width. However, unlike the previous code, here, the Application.InchesToPoints(2.5) line converts 2.5 inches to points first and then sets the column width of the B column.

Now, the “Books” column is wide enough to display the data inside it clearly.

Excel VBA to Set Column Width and Wrap Text

In this example, we will not only change the column width of the B column but also wrap the text inside the column’s cells so that they are clearly visible.

The code below will set the column width and wrap the text inside the cells of that particular column.

Sub ColumnWidth_WrapText()
Dim ws As Worksheet
Set ws = Worksheets("ColumnWidth_WrapText")
With ws.Range("B:B")
.ColumnWidth = 20
.RowHeight = 30
.WrapText = True
End With
End Sub

This is a VBA macro code written to adjust the column width and row height of column B in the worksheet named “ColumnWidth_WrapText”. Additionally, the code sets the WrapText property to True for the selected range so that the texts inside the cells are clearly visible.

Now, we can see that the column width and row height of the B column have changed. Also, the texts inside the B10 and B11 cells are wrapped inside the cells.

Things to Remember

• The minimum width is 0 and the maximum width is 255.
• Excel does not take any negative width.
• The unit of column width in Excel is in points (pt). One point is equal to 1/72 inch, or approximately 0.35 millimeters. Excel uses points as the default unit for specifying column width, but it also allows users to specify column width in other units of measurement, such as inches or centimeters, through the use of the column width dialog box or the column width property in VBA code. However, regardless of the unit of measurement used to specify column width, Excel always converts the value to points for internal use.

Download Practice Workbook

You can download the practice book here.

Conclusion

In this article, we have talked about how to set column width in Excel VBA. This article will allow users to understand the Worksheets.ColumnWidth property properly and then apply the VBA codes to change the column width according to their needs. Don’t forget to share your experience in the comment section. Happy Excelling!