In this lesson you can learn how to autofit columns width and to keep the width of the columns when copying data in Excel. You can change the column widths in Excel using the mouse. For example if I want to change the column width of column A.
How to autofit a column width?
Place the mouse cursor on the line between column A and column B
The mouse cursor will change to a double-headed arrow.
Double left click with the mouse button and the column will auto adjust.
Note: You can also manually adjust the length of a column. Instead of double clicking with the left mouse button, click the left mouse button once and drag to change the width accordingly.
How to keep a width of columns when copying?
When you copy part or all of any table to another workbook or worksheet to another location, by default, Excel places the copied content in the cells of a standard width. If your cells are wider or narrower, you must change it back manually after copying. Let's see how to copy cells, retaining the original column width.
To copy a table cell, first select it.
Press the Ctrl key, hold it and press C.
Set the cursor in the cell, where you want to paste the data. Press the Ctrl key, hold it and press V. The content will be pasted, but without keeping the column widths.
At the bottom of the cells, which copied the contents of the table, we see the so-called smart tag. Click on the icon and the menu as shown below.
Column widths will be adjusted to the pattern.
How to fit a column width manually?
You can also change the column width manually.
To do this, right-click on the column header.
Tip: You can do the same by clicking on Ribbon> Home> Format
Select the Column Width option.
The current column width appears in the text box.
Change to the value you expect.
Confirm by clicking on Ok.
The column width will change to whatever you selected.
How to fit a column width using VBA?
You can use the Columns.AutoFit method in VBA to automatically adjust the width of a column to fit its contents. Here's an example of how you can do this for a specific column in your worksheet:
Sub AutoFitColumn()
Worksheets("Sheet1").Columns("A").AutoFit
End Sub
This code will auto-fit the width of column A on the "Sheet1" worksheet. You can replace "A" with the letter of the column that you want to auto-fit, and replace "Sheet1" with the name of the worksheet that you want to use.
You can also use a loop to auto-fit the width of multiple columns:
Sub AutoFitAllColumns()
Dim i As Integer
For i = 1 To 10
Worksheets("Sheet1").Columns(i).AutoFit
Next i
End Sub
This code will auto-fit the width of columns A through J on the "Sheet1" worksheet. You can adjust the number "10" to the number of columns that you want to auto-fit.