# Setting fixed widths to columns, different widths for different columns

#### CasperA

##### Board Regular
Does anyone know how to set a fixed width to a column, different widths for different columns?

CA

### Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.
Hi,

You could use a macro something like this:
Code:
``````Private Sub Worksheet_Change(ByVal Target As Range)

Dim arrWidths As Variant
Dim i As Long

Application.EnableEvents = False

arrWidths = Array(10, 20, 30, 40, 30, 20, 10)

For i = 1 To UBound(arrWidths)
Columns(i).ColumnWidth = arrWidths(i - 1)
Next

Application.EnableEvents = True

End Sub[/sub]

If you run that it will set the widths of as many columns as you insert into the array. It sets seven as is.

You could make that an ordinary macro to be run on request.

Alternatively, you can set the column widths and then protect the worksheet specifying that the columns and rows cannot be formatted. You can then lock that using a password.``````

Thanks a lot!

CA

Does anyone know how to set a fixed width to a column, different widths for different columns?
You can do that with a single line of code. Consider this...

Code:
``Range("A1:G1").ColumnWidth = Array(10, 20, 30, 40, 30, 20, 10)``

NOTE: You must use Range (like I show above) to define the columns to be resized, if you try and use Columns("A:G") in place of Range("A1:G1"), Excel will lock up (I'm not sure why, I just know that is what happens to me when I try).

Thank you, that worked fine!

Thanks Rick, that is one to add to my list

Replies
3
Views
645
Replies
4
Views
333
Replies
3
Views
246
Replies
0
Views
266
Replies
3
Views
559

1,203,383
Messages
6,055,107
Members
444,763
Latest member
Jaapaap

### 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.

### Which adblocker are you using?

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

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