Fixed Column Width/Autofit Row

[SDave]

Does anyone know of or have a VBA code that enables you to fix column width yet autofit rows?!

I would like columns E:G to have their width fixed to 75, yet still allow rows E12:G16 to autofit.

I was previously using the following so that columns E:G autofitted, as and when data was pulled into their corresponding cells, however I have been asked to slightly tweak things:

Private Sub Worksheet_Calculate()
Columns("E:G").EntireColumn.AutoFit
End Sub

Any help would be much appreciated.

Thanks!

 

[VoG]

Try

Private Sub Worksheet_Calculate()
With Columns("E:G")
    .AutoFit
    .WrapText = True
End With
End Sub

 

[mikerickson]

Are you talking about column widht being fixed and row height being autofit.

Range("E:G").ColumnWidth = 75
Range("12:16").Rows.Autofit

Or are you talking about Columns E:G having one width except for rows 12:16, which have different widths. No, because if any cell in a column is wider than the others, then its not a column.

 

[SDave]

Mike,

Yes - column width being fixed and row height being autofit.

I've used the code that you've supplied, which by the way works great - much appreciated. Is there anyway I could also specify that the autofit range should also wrap text?!

VOG, thank you for your help - again it's much appreciated, the code you supplied worked much in the same manner as my original code?!

 

[mikerickson]

VOG's code shows the syntax for wrapping text.

 

[SDave]

Got it - thanks!