Column width in Excel 2003

[AnneK]

My Bureau Chief has an Excel 2003 document that he receives from our
finacial unit. When he tries to print the document out it shows ###### in
different cells throughout the entire document. In the past when he used
Excel 2000 he would just double click on the right hand side of the top of
the column and it would expand the column one character larger and would
print the whole number in each of those cells. In Excel 2003 when he does
this, it still shows the #####'s and he has to physically go in and move the
column over ever so slightly to get it to print out correctly. When there
are numerous columns that have this issue, it is very time consuming. Is
there another way to correct this that is fast and easy rather than manually
having to expand the column? He also does not want to make all of the
columns the same width as this would take up quite a bit more paper when
printing out.

PS Throughout each column there are different font sizes.

 

[RagDyer]

Select all the columns, then, from the Menu Bar:

<Format> <Columns> <AutoFit>

Each column will be *individually* sized to display the widest entry in that
column.

 

[Gord Dibben]

Try selecting all the columns in the range and Format>Column>Autofit Selection.

Which is what the double-click should be doing...........fitting to selection,
not expanding one character larger as you state.


Gord Dibben MS Excel MVP

 

[Thomas M.]

I think that what she meant by "one character larger" is that
double-clicking the column border will expand the column so that it is one
character larger than the longest value in the column.

I work for the same organization as Anne, and while I'm not working this
particular issue I have seen the spreadsheet in question. RagDyer's
recommendation of selecting multiple columns--and maybe even all columns,
depending on how you want things to look--and then going to Format > Column

AutoFit Selection may be a viable work-around, but I'd be interested in

knowing the root cause of the behavior.

Double-clicking the column border does indeed auto size the column so that
all the values can be seen. However, even after double-clicking the column
borders the ####### mask can still be seen in Print Preview for certain
values, and the spreadsheet will print out that way as well. I think the
deeper question is, "If double-clicking works to expand the columns and make
all the values visible in Normal view, then why aren't those values
displayed properly in Print Preview or on the actual print out?"

--Tom

 

[Gord Dibben]

I think that would be a function of your printer driver and the way it
interfaces with Excel.

That's why Print Preview is such a handy feature.


Gord

 

[Rick Rothstein \(MVP - VB\)]

I've read through this thread and I think you may be able to use this macro.
It lets you specify a "buffer factor" value (a number greater than 1) to
increase a column width by (for example, 1.1 will make a column 10% wider
than the AutoFit width) to create "extra" white space for the text UNLESS
the column width is already the buffer factor or more larger, in which case
that width is preserved. The buffer factor is specified in the Const
statement... I used 10% for my tests; but, perhaps, you can use 1.05 (5%
extra white space)... you will have to test to see what minimum buffer
factor you can get away with.

Sub SizeToFit()
Dim R As Range
Dim X As Long, z
Dim ColWidth As Double
Const Tolerance As Double = 1.1 '10% extra room
Set R = Worksheets("Sheet1").UsedRange
For X = R.Column To R.Columns.Count
ColWidth = Columns(X).ColumnWidth
If Worksheets("Sheet1").Cells(Rows.Count, X).End(xlUp).Row = 1 Then
If Worksheets("Sheet1").Cells(Rows.Count, X).Value = "" Then
GoTo Continue
End If
End If
Columns(X).AutoFit
If Tolerance * Columns(X).ColumnWidth < ColWidth Then
Columns(X).ColumnWidth = ColWidth
Else
Columns(X).ColumnWidth = Tolerance * Columns(X).ColumnWidth
End If
Continue:
Next
End Sub

Rick

 

[Thomas M.]

Rick,

Thanks for the code! The first two columns of the spreadsheet are labels
for the rows and we don't want to change the widths of those columns, so I
added an IF statement at the top of the FOR loop as follows:

For X = R.Column To R.Columns.Count
If X < 3 Then
GoTo Continue
End If

Otherwise, I did not need to change anything.

--Tom

 

[Rick Rothstein \(MVP - VB\)]

You are quite welcome... I'm glad you could make use of the code. By the
way, instead of adding your 3-line If-Then code block, you could have just
changed the For-Next statement to this...

For X = 3 To R.Columns.Count

I wasn't sure where your data was, so I just used the UsedRange as my
limits; but since you know the beginning column is 1, you can simply adjust
the For-Next lower limit to avoid the first 2 columns.

Rick

 

[Thomas M.]

I tried something like that. Actually, what I did was to define a named
range that started with the 3rd column and covered the entire area of data,
and then I inserted that named range into the code. Done right, I'm sure
that would also fix the problem, but I'm not real familiar with Excel VBA
these days and so I was having a problem making it loop the correct number
of times. Clearly, I had a bug in my code, so I finally figured that
switching my approach and using a simple IF statement was easier than
keeping the more complicated code and trying to find the bug. For whatever
reason, changing the lower limit on the FOR loop didn't occur to me, but it
is a more efficient solution.

Thanks for the help.

 

[aaronroessler]

I've read through this thread and I think you may be able to use this macro.
It lets you specify a "buffer factor" value (a number greater than 1) to
increase a column width by (for example, 1.1 will make a column 10% wider
than the AutoFit width) to create "extra" white space for the text UNLESS
the column width is already the buffer factor or more larger, in which case
that width is preserved. The buffer factor is specified in the Const
statement... I used 10% for my tests; but, perhaps, you can use 1.05 (5%
extra white space)... you will have to test to see what minimum buffer
factor you can get away with.

Sub SizeToFit()
  Dim R As Range
  Dim X As Long, z
  Dim ColWidth As Double
  Const Tolerance As Double = 1.1  '10% extra room
  Set R = Worksheets("Sheet1").UsedRange
  For X = R.Column To R.Columns.Count
    ColWidth = Columns(X).ColumnWidth
    If Worksheets("Sheet1").Cells(Rows.Count, X).End(xlUp).Row = 1 Then
      If Worksheets("Sheet1").Cells(Rows.Count, X).Value = "" Then
        GoTo Continue
      End If
    End If
    Columns(X).AutoFit
    If Tolerance * Columns(X).ColumnWidth < ColWidth Then
      Columns(X).ColumnWidth = ColWidth
    Else
      Columns(X).ColumnWidth = Tolerance * Columns(X).ColumnWidth
    End If
Continue:
  Next
End Sub

Rick

I would love to use this code as well, but i am getting an error. I am
using Excel 2004 on Mac OS X Leopard. The error i get is on this
line... Const Tolerance As Double = 1.1  '10% extra room - Syntax
error?

any ideas?

Thanks in Advance,
Aaron

 

[aaronroessler]

I would love to use this code as well, but i am getting an error. I am
using Excel 2004 on Mac OS X Leopard.  The error i get is on this
line...  Const Tolerance As Double = 1.1  '10% extra room  - Syntax
error?

any ideas?

Thanks in Advance,
Aaron

FIXED IT! had to remove the "extra room" comment. very new to VBA,
simple mistake.

 

[Rick Rothstein \(MVP - VB\)]

I would love to use this code as well, but i am getting an error. I am

FIXED IT! had to remove the "extra room" comment. very new to
VBA, simple mistake.

I've never used the Mac version of Excel so for my own knowledge... are you
saying that VBA in Mac XL2004 doesn't allow for in line comments? I am
surprised by that. Can you use comments (delineated by the apostrophe) by
themselves on their own line?

Rick