Cycle thru & format all columns

  • Thread starter Thread starter Arawn
  • Start date Start date
A

Arawn

I need to cycle thru each column, autofit it, and add 2 character
spaces.

My current code is:

Cells.Select
Selection.EntireColumn.Hidden = False
Selection.EntireRow.Hidden = False

Cells.EntireColumn.AutoFit
Range("A1").Select
Wide = Selection.ColumnWidth
Selection.ColumnWidth = Wide + 2
Range("B1").Select
Wide = Selection.ColumnWidth
Selection.ColumnWidth = Wide + 2
Range("C1").Select
Wide = Selection.ColumnWidth
Selection.ColumnWidth = Wide + 2
Range("D1").Select
Wide = Selection.ColumnWidth
Selection.ColumnWidth = Wide + 2

Etc, etc, etc, all the way to

Range("IV1").Select
Wide = Selection.ColumnWidth
Selection.ColumnWidth = Wide + 2

Although this is functional, it looks like a mess. Any help in
cleaning it up would be greatly appreciated.

~Arawn
 
From vbe HELP. Change *2 to +2.
ColumnWidth Property
See Also Applies To Example Specifics
Returns or sets the width of all columns in the specified range. Read/write
Variant.

Remarks
One unit of column width is equal to the width of one character in the
Normal style. For proportional fonts, the width of the character 0 (zero) is
used.

Use the Width property to return the width of a column in points.

If all columns in the range have the same width, the ColumnWidth property
returns the width. If columns in the range have different widths, this
property returns Null.

Example
This example doubles the width of column A on Sheet1.

With Worksheets("Sheet1").Columns("A")
.ColumnWidth = .ColumnWidth * 2
End With
 
This might work:

Sub colwidth()
Dim clastcol As Integer

lastcol = ActiveSheet.Cells(1, 255).End(xlToLeft).Column

Cells.Select
With Selection
.EntireColumn.Hidden = False
.EntireRow.Hidden = False
.EntireColumn.AutoFit
End With

For i = 1 To lastcol
Cells(1, i).ColumnWidth = Cells(1, i).ColumnWidth + 2

Next i

End Sub
 
Thanks for the help guys, both solutions worked (One for each
application that I have).

The help is greatly appreciated!

~Arawn
 
Back
Top