Read and Write Column Width Function

[jstiene]

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

 

[cerfani]

You can autofit columns too btw

 

[jstiene]

Autofit is half assed and unpredictable