Results 1 to 4 of 4
  1. #1
    New Lounger
    Join Date
    Oct 2003
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts

    How to set column width in points (Excel 2000 +)

    Is there any way to set a column width in points? You can set "ColumnWidth", but "Width", which is in points, is read-only. ColumnWidth takes into account font metrics so I don't want to necessarily use that unless someone has a good idea about how to accomplish this. Thanks!

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: How to set column width in points (Excel 2000 +)

    You could use something like this to set (for example) col A to 100 points

    <pre>With Columns("a:a")
    .ColumnWidth = 100 * .ColumnWidth / .width
    End With</pre>


    Steve

  3. #3
    New Lounger
    Join Date
    Oct 2003
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: How to set column width in points (Excel 2000 +)

    I did some computational arithmetic that seems to work, generally. The code looks something like this:

    Set rng = ws.Range(ws.Cells(1, n + 1), ws.Cells(65536, n + 1)) ' get the column
    rng.ColumnWidth = 100# ' we must convert a ColumnWidth (std font width) to Width (points)
    dblFactor = 100# / rng.Width ' so get the factor to set ColumnWidth below (Width is read-only)
    rng.ColumnWidth = dblArray(n) * dblFactor

    where

    Dim rng as Range and
    Dim ws as Worksheet and
    Dim dblFactor as Double and where
    n=the target column, and
    dblArray(n) is the desired column in points values

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: How to set column width in points (Excel 2000 +)

    I envisioned using a line of VB code like this:
    <pre>Call SetColumnPts("A:H", 100)</pre>


    to change cols A-H to 100 pts. It would call this routine:

    <pre>Sub SetColumnPts(sRng As String, lPts As Long)
    Dim x As Integer
    For x = 1 To Range(sRng).Columns.Count
    With Range(sRng).Cells(1, x).EntireColumn
    .ColumnWidth = lPts * .ColumnWidth / .Width
    End With
    Next x
    End Sub</pre>



    Steve

Posting Permissions

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