Setting the Width of a column

[Nirmal Singh]

I need to set the width of a column. I am using the following code

ActiveSheet.Columns("A:A").Width = 57

This should set the width of the column to approximately 0.8 inches (57/72).

I am getting the following error message:

Run-Time error '1004'

Unable to set the width property of the Range class.

Why not?

Nirmal

 

[Bernie Deitrick]

Nirmal,

Because the property .Width is read only. The property .ColumnWidth can be
modified.

HTH,
Bernie
MS Excel MVP

 

[Norman Jones]

Hi Nirmal,

Try:

ActiveSheet.Columns("A:A").ColumnWidth = 57

 

[Nirmal Singh]

Because the property .Width is read only. The property .ColumnWidth can be
modified.

Thanks for that Bernie. I still need to set my columns to specific sizes to ensure that all my data
gets printed properly.

The ColumnWidth property is set in terms of the number of characters that will fit in a column. How
can I use this to set the absolute width of a column?

Nirmal

 

[Bernie Deitrick]

Nirmal,

You can get close (but never get exact due to limitations on the accuracy of
the character size) using something like:

Sub Macro1()
Dim NewWidth As Double

NewWidth = 17

With Range("A1").EntireColumn
MsgBox "Column A was this wide: " & .Width
.ColumnWidth = NewWidth * .ColumnWidth / .Width
.ColumnWidth = NewWidth * .ColumnWidth / .Width
MsgBox "Column A is this now wide: " & .Width
End With

End Sub

Note that the .ColumnWidth is set twice: the iteration is necessary for some
reason....

HTH,
Bernie
MS Excel MVP

 

[Nirmal Singh]

You can get close (but never get exact due to limitations on the accuracy of
the character size) using something like:

Sub Macro1()
Dim NewWidth As Double

NewWidth = 17

With Range("A1").EntireColumn
MsgBox "Column A was this wide: " & .Width
.ColumnWidth = NewWidth * .ColumnWidth / .Width
.ColumnWidth = NewWidth * .ColumnWidth / .Width
MsgBox "Column A is this now wide: " & .Width
End With

End Sub

Thanks Bernie, that's close enough for me.

Nirmal