Results 1 to 8 of 8
  1. #1
    Star Lounger
    Join Date
    Feb 2001
    Location
    Auckland, North Island, New Zealand
    Posts
    73
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Specific column width (any?)

    I have just discovered that Excel measures its column widths in character widths. Is there a straightforward way of getting column widths of say 22.7 points - which is what I need in this specific case. I know I can do it by printing then measuring and then adjusting the width by a multiplication factor, but is there a better way?
    Thanks.

  2. #2
    Super Moderator WebGenii's Avatar
    Join Date
    Jan 2001
    Location
    Redcliff, Alberta, Canada
    Posts
    4,066
    Thanks
    2
    Thanked 5 Times in 5 Posts

    Re: Specific column width (any?)

    Do you mean like autofitting a column? or are you looking for a method of setting column widths for a large number of columns simultaneously?

    Cheers
    [b]Catharine Richardson (WebGenii)
    WebGenii Home Page
    Moderator: Spreadsheets, Other MS Apps, Presentation Apps, Visual Basic for Apps, Windows Mobile

  3. #3
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Specific column width (any?)

    No really better way I'm afraid.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Specific column width (any?)

    To be more precise, column widths are measured in characters in the Normal style. The idea behind it is probably that if you change the font size of the normal style, a column will still contain the same number of characters, so that you won't have to adjust the column widths manually.

    Be aware that the relation between column width in characters and points is not entirely linear - see attached chart (it's for Arial 16 points). You can determine the width of the active cell easily using the Visual Basic Editor (without programming):
    <UL><LI>Activate the VBE (Alt+F11)
    <LI>Activate the Immediate window (Ctrl+G)
    <LI>Type
    ? ActiveCell.Width
    to get the width in points, and
    ? ActiveCell.ColumnWidth
    to get the width in characters.[/list]You will find that the relation between the two is different for very narrow columns than for wider columns.
    Attached Images Attached Images
    • File Type: gif x.gif (1.8 KB, 0 views)

  5. #5
    Star Lounger
    Join Date
    Feb 2001
    Location
    Auckland, North Island, New Zealand
    Posts
    73
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Specific column width (any?)

    Hi Catharine,

    Yes, it's to set a large number of columns simultaneously, to match some dimensions on printed stationery (in this case a teacher's markbook).
    Jan Karel Pieterse has replied that the try and then calculate method I suggested is the only way. I'm really surprised though as Microsoft Office is usually so flexible in the way it handles different units. To have to measure in a variable unit like font-dependent character spacing seems primitive. Surely there are others who want to lay-out documents with precise column widths? But then, if there was a real need it would have been fixed before version 10!

    Regards,

    Jim.

  6. #6
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Specific column width (any?)

    A more precise method would be to use a macro like this one:

    Sub SetColumnWidthInches()
    Dim dCellwidth As Double
    Dim dColwidth As Double
    Dim dInches As Double
    Dim dDesiredPoints As Double
    dInches = InputBox("Please enter desired column width (inch)")
    dDesiredPoints = Application.InchesToPoints(dInches)
    dCellwidth = ActiveCell.Width
    dColwidth = ActiveCell.ColumnWidth
    ActiveCell.ColumnWidth = dDesiredPoints / dCellwidth * dColwidth
    'Repeat in case small rounding error occurred
    dCellwidth = ActiveCell.Width
    dColwidth = ActiveCell.ColumnWidth
    ActiveCell.ColumnWidth = dDesiredPoints / dCellwidth * dColwidth
    End Sub
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  7. #7
    Super Moderator WebGenii's Avatar
    Join Date
    Jan 2001
    Location
    Redcliff, Alberta, Canada
    Posts
    4,066
    Thanks
    2
    Thanked 5 Times in 5 Posts

    Re: Specific column width (any?)

    Thanks for the background - sometime it really helps to understand what people are asking. Not that I can give any better answers than you've already been given.
    Have you thought about taking the data over into Word?

    Cheers
    [b]Catharine Richardson (WebGenii)
    WebGenii Home Page
    Moderator: Spreadsheets, Other MS Apps, Presentation Apps, Visual Basic for Apps, Windows Mobile

  8. #8
    Star Lounger
    Join Date
    Feb 2001
    Location
    Auckland, North Island, New Zealand
    Posts
    73
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Specific column width (any?)

    Thank you. This is very helpful.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •