Why isn't this formatting correctly?

litlmike

New Member
Joined
Jul 13, 2018
Messages
12
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?

Code:
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
 

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.
What happens with the code below?

Code:
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
 
Last edited:
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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!
 
Upvote 0
If your merged cells are horizontally merged have a look at center across selection under the alignment tab.
 
Upvote 0

Forum statistics

Threads
1,213,585
Messages
6,114,513
Members
448,575
Latest member
hycrow

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top