Macro to format a specific worksheet

Question

I am still new to VBA. I recorded several actions that I need to perform to ensure that a specific worksheet is formatted properly.

Row 1 contains headers. Other than the headers, there is no data in the sheet when this should be run. I know that the raw code from the recording can be shrunk down, but I would appreciate some assistance in figuring that out.

Sub MasterSheetFormatTest()
'
' MasterSheetFormatTest Macro
'
'
 Columns("A:A").ColumnWidth = 7
 Columns("B:B").ColumnWidth = 6
 Columns("C:C").ColumnWidth = 17.14
 Columns("D:D").ColumnWidth = 13.57
 Columns("E:E").ColumnWidth = 2.71
 Columns("F:F").ColumnWidth = 21.43
 Columns("G:G").ColumnWidth = 16.43
 Columns("H:H").ColumnWidth = 7.86
 Columns("I:I").ColumnWidth = 13.43
 Columns("J:J").ColumnWidth = 25.14
 Columns("K:K").ColumnWidth = 39.29
 Columns("L:L").ColumnWidth = 34.14
 Columns("M:M").ColumnWidth = 23.14
 Columns("N:N").ColumnWidth = 5.57
 Columns("O:O").ColumnWidth = 17.14
 Columns("P:P").ColumnWidth = 17.14
 Columns("Q:Q").ColumnWidth = 8.14
 Columns("R:R").ColumnWidth = 17.71
 Columns("S:S").ColumnWidth = 22.57
 Columns("T:T").ColumnWidth = 20.43
 Columns("U:U").ColumnWidth = 15.57
 Columns("V:V").ColumnWidth = 13.43
 Columns("W:W").ColumnWidth = 13.43
 Columns("X:X").ColumnWidth = 10.86
 Columns("Y:Y").ColumnWidth = 8.57
 Columns("Z:Z").ColumnWidth = 7.57
 Columns("AA:AA").ColumnWidth = 7.57
 Columns("AB:AB").ColumnWidth = 15
 Columns("AC:AC").ColumnWidth = 9.29
 Columns("AD:AD").ColumnWidth = 15.86
 Columns("AE:AE").ColumnWidth = 67.29
 Range("A1:AE1").Select
 Range("AE1").Activate
 With Selection
 .HorizontalAlignment = xlCenter
 .VerticalAlignment = xlBottom
 .WrapText = False
 .Orientation = 0
 .AddIndent = False
 .IndentLevel = 0
 .ShrinkToFit = False
 .ReadingOrder = xlContext
 .MergeCells = False
 End With
 With Selection
 .HorizontalAlignment = xlCenter
 .VerticalAlignment = xlCenter
 .WrapText = False
 .Orientation = 0
 .AddIndent = False
 .IndentLevel = 0
 .ShrinkToFit = False
 .ReadingOrder = xlContext
 .MergeCells = False
 End With
 ActiveWindow.Zoom = 90
 ActiveWindow.Zoom = 80
 ActiveWindow.Zoom = 70
 Range("A2").Select
 With ActiveWindow
 .SplitColumn = 0
 .SplitRow = 1
 End With
 ActiveWindow.FreezePanes = True
End Sub

• Raystafarian: Are the columns autofit?
• Phrancis: There is really not much to say about this, the code doesn't "do" anything besides telling Excel how to format a sheet...
• 200_success: Please edit the title: tell us what you mean by "this". (See How to Ask.)
• Iron Man: @Raystafarian - No. That is why they are all listed out. I need each column to be the exact width listed.
• Iron Man: @Phrancis - That is true, but I posted this to see if there was a better way to achieve the results.

Answer 1

This is repeated for each column's width:

Columns("A:A").ColumnWidth

It could just be:

Columns("A").ColumnWidth

---

The code repeats this block of code almost verbatim twice in a row, with no change in the Selection:

With Selection
 .HorizontalAlignment = xlCenter
 .VerticalAlignment = xlCenter
 .WrapText = False
 .Orientation = 0
 .AddIndent = False
 .IndentLevel = 0
 .ShrinkToFit = False
 .ReadingOrder = xlContext
 .MergeCells = False
End With

With the only difference being .VerticalAlignment = xlBottom (1st one) and .VerticalAlignment = xlCenter (2nd one).

You should be able to remove one of the two altogether and get identical results, as the 2nd one should overwrite changes made by the 1st one.

---

This repeats the same command with varying zoom levels:

ActiveWindow.Zoom = 90
ActiveWindow.Zoom = 80
ActiveWindow.Zoom = 70

I would say unless you need to see that is actually zooms progressively, just do ActiveWindow.Zoom = 70 by itself.

• Iron Man: The two "With" sections are actually different. Those are aligning the top row of cells to center and center. I would assume that all the other components can be removed as well? I can remove the two ActiveWindow.Zoom's to show the one that results in 70.
• Phrancis: Ah I just noticed the subtle difference on VerticalAlignment. The 2nd block's value should override the 1st block, so I'd remove the first one. I'll edit my answer

Answer 2

So, you have a list of specific column widths. Cool. My advice would be: Take these values, and put them in a spreadsheet somewhere like so:

Column Number | Column Width (px) 
-------------------------------- 
1 | 7 
-------------------------------- 
2 | 6 
-------------------------------- 
3 | 17.14 

Now, you can create an array like so:

Dim columnWidths as Variant
 columnWidths = Array()
Dim widthsTable as Range
Set widthsTable = sheets("SheetName").Range("TableRange") '/ substitue actual sheet names / ranges
 columnWidths = widthsTable

Then, you have an array of data laid out like a table array(x,y) = row x, column y of your range.

So, columnWidths(1,1) = "Column Number".

columnWidths(2,1) = "1".

columnWidths(2,2) = "7" etc.

Then, we can iterate through your table:

Dim ix as Long, col as Long, colWidth as Double
 For ix = LBound(columnWidths, 1) + 1 to UBound(columnWidths, 1) '/ +1 to avoid the header
 col = columnWidths(ix, 1)
 colWidth = columnWidths(ix, 2)
 columns(col).columnWidth = colWidth
 Next ix

L/UBound are measuring the size of your table, so you can make your range as big as you like and you won't have to change this part of the code at all.