Read and Write Column Width Function


Board Regular
Aug 5, 2005
Since I constantly find myself tweaking column widths after running routines, I thought I would just write code that writes column code. Basically if a sheet is the way you want it, you determine the header row (just for commenting purposes) and run this to build code that figures out all your column widths in that sheet along with a comment about header value of each column. Obviously you shouldn't keep using the Sub name it creates, but change them for every sheet.

Sub ReadColumnWidths()
Dim g As Integer, LastCol As Integer, Val1 As String
Dim St1 As String, St2 As String, Add1 As String
Dim ColWidth As Integer, HeaderRow As Integer
HeaderRow = 1 'ActiveCell.Row
LastCol = ActiveSheet.UsedRange.Columns.Count
For g = 1 To LastCol
Add1 = Cells(HeaderRow, g).Address(False, False)
ColWidth = Range(Add1).ColumnWidth
Val1 = Range(Add1).Value
St1 = "Range(" & Chr(34) & Add1 & Chr(34) & ").ColumnWidth = " & ColWidth & " '" & Val1
St2 = St2 & vbCr & St1 'debug.print St1
Next g
St2 = vbCr & "Sub FormatColumns()" & vbCr & St2 & vbCr & vbCr & "End Sub"
Debug.Print St2 'MsgBox St2
End Sub

It spits out something like this into the immediate window:

Sub FormatColumns()

Range("A1").ColumnWidth = 15 'Name
Range("B1").ColumnWidth = 12 'Week Starting
Range("C1").ColumnWidth = 28 'Code
Range("D1").ColumnWidth = 55 'Project Name

End Sub

Some videos you may like

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.

Watch MrExcel Video

Forum statistics

Latest member

This Week's Hot Topics