Column Width

[billandrew]

Have a data set which results in column widths required to be set at a specific width. Is there a way to perform this task, the data can extend to many more than the example below.

Auto fit does not work since the data in some cells may be larger.

example


Range("A1").ColumnWidth = 14.5
Range("B1").ColumnWidth = 22#
Range("C1").ColumnWidth = 23.5
Range("D1").ColumnWidth = 8.5
Range("E1").ColumnWidth = 28.5
Range("F1").ColumnWidth = 12.5

 

[Michael M]

You mean like this

Columns("A:A").AutoFit
 Columns("B:B").AutoFit
 Columns("C:C").AutoFit

 

[billandrew]

Guess there is no other way.

Thank you

 

[Fluff]

Another option would be

Sub ColWidth()

   Dim Col As Range
   For Each Col In Range("A1", Cells(1, Columns.Count).End(xlToLeft))
      Col.ColumnWidth = Len(Col)
   Next Col

End Sub

This will set the col width depending on the length of the value in row 1 for each col. If needed you could also use this

Col.ColumnWidth = Len(Col)+3

to make the cols a little bit wider

 

[billandrew]

Thank you!

 

[Fluff]

Your welcome