Column Width Macro Not Working

[DJ'sGiGi]

Greetings,

I recorded a simple macro to adjust the column widths of my worksheet. However, when I run the macro ONLY column J:O are adjusted to the column width specified (D:J are sizing much smaller that I need.) Does anyone have ideas on what's happening? The macro is below:

Sub ColumnWidth()
'
' ColumnWidth Macro
'

'
Columns("D:D").Select
Range("D2").Activate
Selection.ColumnWidth = 58.57
Columns("E:E").Select
Range("E2").Activate
Selection.ColumnWidth = 26.86
Columns("F:F").Select
Range("F2").Activate
Selection.ColumnWidth = 24.14
Columns("G:G").Select
Range("G2").Activate
Selection.ColumnWidth = 36.57
Columns("H:H").Select
Range("H2").Activate
Selection.ColumnWidth = 16.14
Columns("I:I").Select
Range("I2").Activate
Selection.ColumnWidth = 23
Columns("J:J").Select
Range("J2").Activate
Selection.ColumnWidth = 15.14
Columns("K:K").Select
Range("K2").Activate
Selection.ColumnWidth = 17
Columns("L:L").Select
Range("L2").Activate
Selection.ColumnWidth = 16.71
Columns("M:M").Select
Range("M2").Activate
Selection.ColumnWidth = 16.43
Columns("N:N").Select
Selection.ColumnWidth = 15.43
Columns("O:O").Select
Selection.ColumnWidth = 21.57
End Sub


Thanks a bunch.

 

[DJ'sGiGi]

Issue Resolved. I revised the macro and it works perfectly. New one below.

Columns("A").ColumnWidth = 14.29
Columns("B").ColumnWidth = 29.71
Columns("C").ColumnWidth = 17.57
Columns("D").ColumnWidth = 58.57
Columns("E").ColumnWidth = 26.86
Columns("F").ColumnWidth = 24.14
Columns("G").ColumnWidth = 36.57
Columns("H").ColumnWidth = 16.14
Columns("I").ColumnWidth = 23
Columns("J").ColumnWidth = 15.14
Columns("K").ColumnWidth = 17
Columns("L").ColumnWidth = 16.71
Columns("M").ColumnWidth = 16.43
Columns("N").ColumnWidth = 15.43
Columns("O").ColumnWidth = 21.57

 

[Rick Rothstein]

Issue Resolved. I revised the macro and it works perfectly. New one below.

Columns("A").ColumnWidth = 14.29
Columns("B").ColumnWidth = 29.71
Columns("C").ColumnWidth = 17.57
Columns("D").ColumnWidth = 58.57
Columns("E").ColumnWidth = 26.86
Columns("F").ColumnWidth = 24.14
Columns("G").ColumnWidth = 36.57
Columns("H").ColumnWidth = 16.14
Columns("I").ColumnWidth = 23
Columns("J").ColumnWidth = 15.14
Columns("K").ColumnWidth = 17
Columns("L").ColumnWidth = 16.71
Columns("M").ColumnWidth = 16.43
Columns("N").ColumnWidth = 15.43
Columns("O").ColumnWidth = 21.57

You can do that with a single line of code, albeit a rather long one, so I used Line Continuation characters to make the line wrapping neater...

Range("A1:O1").ColumnWidth = Array(14.29, 29.71, 17.57, 58.57, 26.86, _
                                   24.14, 36.57, 16.14, 23, 15.14, _
                                   17, 16.71, 16.43, 15.43, 21.57)

Note: Make sure you include a row number for the range (doesn't matter what row number it is as long as you use the same one for both cell references), otherwise you will lock up Excel while it adjusts the column widths over and over again for each cell in those columns.

 

[SR4]

I keep getting

Compile error:
Wrong number of arguments or invalid property assignment

An example line of code where it stops:
Columns("D").ColumnWidth = 5.71

Does it matter whether I have data Formatted as a Table?

 

[Alex Blakenburg]

I think you are better off starting a new thread, this one dates back to 2015.
There is nothing wrong with the line of code you posted and it being table shouldn't matter.
So start a new thread, post more of the code you are using and confirm which line of code is higlighted when it errors out and you hit debug.
Also please confirm that the worksheet is not protected.

 

[SR4]

I basically ended up using Range everywhere instead of Columns:

Range("D:D").Select
Selection.ColumnWidth = 5.71

 

[Alex Blakenburg]

I basically ended up using Range everywhere instead of Columns:

Range("D:D").Select
Selection.ColumnWidth = 5.71

As long as you have something that works.
You want to avoid using select when coding, it slows the code down.
Either of these should work and do the same thing:

Range("D:D").ColumnWidth = 5.71
Columns("D").ColumnWidth = 5.71