Setting the Width of a column

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

Nirmal,

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

HTH,
Bernie
MS Excel MVP


"Nirmal Singh" <[email protected]> wrote in
message news:[email protected]...
> 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

Hi Nirmal,

Try:

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

---
Regards,
Norman



"Nirmal Singh" <[email protected]> wrote in
message news:[email protected]...
>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

On Fri, 27 May 2005 11:25:19 -0400, "Bernie Deitrick" <deitbe @ consumer dot org> wrote:

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

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" <[email protected]> wrote in
message news:[email protected]...
> On Fri, 27 May 2005 11:25:19 -0400, "Bernie Deitrick" <deitbe @ consumer
dot org> wrote:
>
> >
> >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
>

On Fri, 27 May 2005 11:47:44 -0400, "Bernie Deitrick" <deitbe @ consumer dot org> wrote:
>
>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