Re: Column width in Excel 2003

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

On Jan 18, 1:51 pm, aaronroess...@xxxxxxxxx wrote:
On Dec 31 2007, 9:41 pm, "Rick Rothstein \(MVP - VB\)"



<rickNOSPAMn...@xxxxxxxxxxxxxxxxx> wrote:
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

"AnneK" <NoReturnema...@xxxxxxx> wrote in message

news:%23l2s7xaSIHA.2268@xxxxxxxxxxxxxxxxxxxxxxx

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.

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

Relevant Pages

  • Re: RfD: BUFFER:
    ... same area of memory. ... BUFFER: permits implementers to create buffers in any available ...    256 BUFFER: Xbuff ... Note that in systems with separated code and data space, ...
    (comp.lang.forth)
  • Re: Ring buffers
    ... create buffer size cells allot ...   full? ... test get-ring. ... It may be simpler for the optimising compiler to ...
    (comp.lang.forth)
  • Re: Surprising Performance Changes with Oracle 11.2.0.1 (Long Post)
    ...   DESCRIPTION VARCHAR2); ... buffer cache is still free. ... When you disable direct I/O you have an extra 4GB of file system ... reads and 813,120 physical block reads during the full tablescan, it ...
    (comp.databases.oracle.server)
  • Re: Bidirectional streams
    ... If you open a stream to a file, write some stuff to the file and now do ...   ... File-position is not defined in terms that differe based on the ... of bytes/characters in the buffer. ...
    (comp.lang.lisp)
  • Re: render error detected, EIR: 0x00000010
    ...      Remove logical context setup. ... command is to clear the absolute buffer. ... I can ignore the error message;-) ... I am more worried about Acer machine where I am not getting XWindows. ...
    (Linux-Kernel)