Setting fixed widths to columns, different widths for different columns

[CasperA]

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

Thank you in advance,

CA

 

[RickXL]

Hi,

You could use a macro something like this:

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.

 

[CasperA]

Thanks a lot!

CA

 

[Rick Rothstein]

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

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

 

[CasperA]

Thank you, that worked fine!

 

[RickXL]

Thanks Rick, that is one to add to my list