Column widths

  • Thread starter Thread starter joe
  • Start date Start date
J

joe

I am trying to write a macro to set column widths and the
last statement (regardless which is last) always overrides
all previous sizings. I recorded the macro, and my
spreadsheet was fine, but when runing that macro, it
worked quite differently. This is my code:
Columns("A:A").Select
Selection.ColumnWidth = 12
Range("A1:H1").Select
Columns("B:B").Select
Selection.ColumnWidth = 15
Range("A1:H1").Select
Columns("C:C").Select
Selection.ColumnWidth = 7
Range("A1:H1").Select
Columns("D:D").Select
Selection.ColumnWidth = 13
Range("A1:H1").Select
Columns("E:E").Select
Selection.ColumnWidth = 11
Range("A1:H1").Select
Columns("F:F").Select
Selection.ColumnWidth = 15
Range("A1:H1").Select
Columns("G:G").Select
Selection.ColumnWidth = 20
Range("A1:H1").Select
Columns("H:H").Select
Selection.ColumnWidth = 5
Range("A1:H1").Select

What is up with that? I have some merged cells, but they
were not a concern when I did the recording.
 
This works for me:

Dim vWidths As Variant
Dim i As Long

vWidths = Array(12, 15, 7, 13, 11, 15, 20, 5)
Application.ScreenUpdating = False
For i = 0 To UBound(vWidths)
Columns(i + 1).ColumnWidth = vWidths(i)
Next i
Application.ScreenUpdating = True
 
Hi Joe
Merged cells will affect setting of the ColumnWidth.
Can you set your column widths first then merge the
required cells.
Also you select the Range("A1:H1") but don't appear to
format it.
You can shorten the code by not selecting the columns;
Columns("A:A").ColumnWidth = 12
and
Columns("B:B,F:F").ColumnWidth = 15
Range("C2:D3").MergeCells = True

HTH

Regards Bob C.
 
Back
Top