VBA ColumnWidth Doesn't work...

[sfaust]

Ok, I've got the following code in a macro that is supposed to autofit a column up to a certain width. The autofit part works fine, but when it gets a column over the specified limit and tried to reset the width, it chokes. Gives me an error "Could not set ColumnWidth property of range object."

Columns(Col).EntireColumn.AutoFit
If Columns(Col & ":" & Col).Width > 528.75 Then
Columns(Col).ColumnWidth = 528.75
End If

Anyone know why this would be?

Edit: FYI, "Col" is a variable passed to this function, which is simply the column letter as a string.

 

[schielrn]

Welcome to the Board!

I assume the 3rd line is giving you the problem?

WHy not try:

Columns(Col).entirecolumn.ColumnWidth = 528.75

Hope that helps.

 

[sfaust]

hm... Thanks, but I get the same result. Here is the actual text of the error:

Run-time error '1004':

Unable to set the ColumnWidth property of the Range class

 

[Tom Urtis]

Maximum attainable column width is 255.

 

[schielrn]

Its because the column width cannot be greater than 255.

http://office.microsoft.com/en-us/excel/HA101375451033.aspx

 

[sfaust]

Ok, I orginally went back to reset this to 255, but I did some more investigating and it's working now but I'm confused.

I first figured out what width I want it to be by simply dragging and then right clicking the column to find out the column size. However when I put it in it came out much smaller. So I set it to what I wanted (100) and made a macro to just report the column width to me via messagebox, and it reported like 528.75. So I figured there was some different type of units or something and just set it to make it 528.75 which didn't work per above.

Now I went back and recorded a macro of the column width being resized to 100, and the macro gave me 100 just like I would suspect it should. So I re-ran the macro to see it it would use that correctly or would convert to the other units and make my column really small. It worked fine.

In short, I went back to my original and set it to 100... and it worked. I'm confused because I don't see any substantial difference in the code I started with and the code I ended with, but one works and one doesn't.

Anyone have any insight?

edit: I think I found the issue, kind of. When I had it report the width to me, I was using Columns("B").Width instead of Columns("B").ColumnWidth. I just made another macro to report both values and they are different (100 vs 528.75). So what is the width for and what does it report?

 

[schielrn]

Range.ColumnWidth Property
Returns or sets the width of all columns in the specified range. Read/write Variant. expression.ColumnWidth
expression A variable that represents a Range object.

Remarks
One unit of column width is equal to the width of one character in the Normal style. For proportional fonts, the width of the character 0 (zero) is used.
Use the Width property to return the width of a column in points.
If all columns in the range have the same width, the ColumnWidth property returns the width. If columns in the range have different widths, this property returns null.


Example
This example doubles the width of column A on Sheet1.
Visual Basic for Applications
With Worksheets("Sheet1").Columns("A") .ColumnWidth = .ColumnWidth * 2End With

Excel Developer Reference
Range.Width Property
Returns a Variant value that represents the width, in units, of the range. expression.Width
expression A variable that represents a Range object.

It looks like Range.ColumnWidth uses points where as Range.Width uses units.

Hope that helps.

 

[MrKowz]

For some reason the other day, it was not letting me use Columns(1).ColumnWidth = xxx.... but it was letting me use Range("A1").ColumnWidth = xxx Give that a shot.

 

[Exeler]

So c.ColumnWidth can not exceed 255 units. I have 3 rows merged with 9 columns. I run a msgbox and get a ColumnWidth of 24.71 and a Width of 133.5. My sheet errors out and says "Run-time error '1004': Unable to set the ColumnWidth property of the Range class".
Any ideas of what's wrong here?

 

[miannini]

I find out I had the worksheet protected. Once I entered a ActiveSheet.Unprotect before the columnwidth statement my problem was solved. Hope this helps