Excel 365, 2019, 2016.
In today’s Excel automation tutorial we will learn how to quickly change the width of a worksheet column as needed – but programmatically using VBA (Visual Basic for Applications).
In order to write VBA code, you’ll need to first and foremost, ensure that your developer tab is enabled. If you are unsure about the procedure, make sure to look into our detailed procedure.
- In your Windows computer, open Microsoft Excel.
- On the main Ribbon hit Developer.
- Hit the Visual Basic Command. This will open the VBA Editor.
- In the left hand side project explorer, highlight a specific sheet into which you’ll insert your code. Alternatively, you can insert a new module or add our code to an existing VBA module.
Setting one column width
In this example, we’ll select one column in the active Worksheet and modify it accordingly.
- Using the VBA Editor, Copy the following code to your worksheet or module.
Sub Set_Column_Width() Dim MySheet As Worksheet Set MySheet = ActiveSheet ' Select one or multiple columns, set your column width as needed With MySheet.Columns("B") .ColumnWidth = 50 End With End Sub
- Run your code by hitting on F5 or pick Run >> Run Sub or Form.
- Save your work by hitting File >> Save or the Disk icon.
Change width of a multiple column range
In this example we pick a range of columns , then change the width.
You can apply the code in the same fashion as described in the previous section.
Sub Set_Column_Range_Width() Dim MySheet As Worksheet Set MySheet = ActiveSheet ' Set your column width as needed With MySheet.Range("A:F") .ColumnWidth = .ColumnWidth * 1.5 End With End Sub
How to reset the columns width in Excel?
Use the following code to revert your Excel column width to the default value:
- Pick a specific column range.
- Use the Standard Width property to reset the column.
Columns("A:D").ColumnWidth = StandardWidth
How to autofit spreadsheet columns with VBA?
Use the following VBA statement to automatically fit the width of your spreadsheets columns according to their contents:
- Pick your specific column range.
- Use the AutoFit method to adjust your column width as shown below:
Starting out with VBA? Make sure to look into VBA basics for Excel guide.