Why isn't this formatting correctly?

[litlmike]

I am trying to create a Macro that formats each column to a particular width. But, when I run the Macro, it just sets all the columns' width to 1. Where am I going wrong?

Sub Macro4()'
' Macro4 Macro
'


'
    Selection.ColumnWidth = 1
    Columns("B:B").Select
    Range("B2").Activate
    Selection.ColumnWidth = 24
    Columns("C:C").Select
    Range("C2").Activate
    Selection.ColumnWidth = 10
    Columns("D:D").Select
    Range("D2").Activate
    Selection.ColumnWidth = 17
    Columns("E:E").Select
    Range("E2").Activate
    Selection.ColumnWidth = 15
    Columns("F:F").Select
    Range("F2").Activate
    Selection.ColumnWidth = 1
    Columns("G:G").Select
    Range("G2").Activate
    Selection.ColumnWidth = 15
    Columns("H:H").Select
    Range("H2").Activate
    Selection.ColumnWidth = 1
End Sub

 

[MARK858]

What happens with the code below?

Sub Macro4()
    Columns("A:A").ColumnWidth = 1
    Columns("B:B").ColumnWidth = 24
    Columns("C:C").ColumnWidth = 10
    Columns("D:D").ColumnWidth = 17
    Columns("E:E").ColumnWidth = 15
    Columns("F:F").ColumnWidth = 1
    Columns("G:G").ColumnWidth = 15
    Columns("H:H").ColumnWidth = 1
End Sub

 

[Gerald Higgins]

Works fine for me.

I'm not code expert, but in each pair of lines like this . . .

Columns("X:X).Select
Range("B2").Activate

You can delete either one of these lines, it doesn't matter which, and the code will have the same result.

Note also that your very first line
Selection.ColumnWidth =1
does not have a target range specified, and so will apply to whichever range you happen to have selected before you run the code.

 

[Fluff]

Do you have merged cells?

 

[litlmike]

Do you have merged cells?

I do have merged cells! How do I get it to work still?

 

[Fluff]

Best answer, get rid of the merged cells.
They are an abomination & will probably cause you no end of pain & heartache.

Failing that, try Mark's code from post#2

 

[litlmike]

Best answer, get rid of the merged cells.
They are an abomination & will probably cause you no end of pain & heartache.

Failing that, try Mark's code from post#2

Alright, I took off the Merged Cells and now it's working. How weird! What's even weirder, I have two worksheets that are duplicates (AM/PM). But, the macro works just fine with merged cells on the 2nd worksheet. How weird!

 

[MARK858]

If your merged cells are horizontally merged have a look at center across selection under the alignment tab.