Change column width according to specific cell content

[RosH]

Hi everyone,

Is there any way by which i be able to know the width of the contents
of a cell. I need to change column width of a column according to the
width of the contents. Autofit would work but according to the longest
content in the column. I need the column width to be set according to
the width of a specific cell content. Thank you in advance. BYE

 

[Guest]

Dear RosH,

The Help section of VBA gives you the answer (see below). You can specify a
specific cell range that you may auto fit!

AutoFit Method
See Also Applies To Example Specifics
Changes the width of the columns in the range or the height of the rows in
the range to achieve the best fit.

expression.AutoFit

expression Required. An expression that returns a Range object. Must be a
row or a range of rows, or a column or a range of columns. Otherwise, this
method generates an error.

Remarks
One unit of column width is equal to the width of one character in the
Normal style.

Example
This example changes the width of columns A through I on Sheet1 to achieve
the best fit.

Worksheets("Sheet1").Columns("A:I").AutoFit
This example changes the width of columns A through E on Sheet1 to achieve
the best fit, based only on the contents of cells A1:E1.

Worksheets("Sheet1").Range("A1:E1").Columns.AutoFit

Regards,
Jan Bart
Excelerate

 

[Tom Ogilvy]

copy the content of the cell to an empty column. autofit that column. Get
the column width from that column. Clear/resize that column. Set the
column in question to that width.