Page 1 of 3 123 LastLast
Results 1 to 15 of 40
  1. #1
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Widen (#####) columns with code (Excel 2000 >)

    I would like to automate the process of widening columns that are too narrow (#####). If column is too narrow (#####), autofit it, BUT no other column widths must be changed in any way. How can I check if a column is too narrow for any data, and how do I automate this?
    Regards,
    Rudi

  2. #2
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Widen (#####) columns with code (Excel 2000 >)

    At it's simplest

    Selection.EntireColumn.AutoFit

    however, that will make columns with a just a few characters narrower.
    -John ... I float in liquid gardens
    UTC -7ąDS

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

    Re: Widen (#####) columns with code (Excel 2000 >)

    You could loop through the columns and
    - Store the current width in a variable.
    - AutoFit the column (look up AutoFit in the VBA help)
    - Compare the new width to original width (the value of the variable).
    - If the new width is LESS than the original width, restore the original width.
    (If the new width is MORE, the column needed to be widened, and if it is the same, nothing has happened)

  4. #4
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Widen (#####) columns with code (Excel 2000 >)

    Hi John,

    The tricky part is Selection!!! Its easy to say Entirecolumn.Autofit, but this must only apply to columns that have #### in them! No other column widths must be adjusted. If my sheet has 100 columns and 73 of those columns have #### due to being too narrow to show the number, it must widen by use of autofit. How do I determine what column has ####?
    Tx
    Regards,
    Rudi

  5. #5
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Widen (#####) columns with code (Excel 2000 >)

    OK...this is a "lateral thinking" solution. I will attempt this code-wise. If I have any hassles with it I'll post back with what I have built!
    Tx
    Regards,
    Rudi

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

    Re: Widen (#####) columns with code (Excel 2000 >)

    I would store the width for each individual column in intW within the loop.

  7. #7
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Widen (#####) columns with code (Excel 2000 >)

    I was trying the get the default sheet column width into intW, which I then in turn compare to each columnwidth after it is set by Autofit. I think I'm missing your point, Hans. <img src=/S/scratch.gif border=0 alt=scratch width=25 height=29>
    -John ... I float in liquid gardens
    UTC -7ąDS

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

    Re: Widen (#####) columns with code (Excel 2000 >)

    What if Rudi has manually changed the widths of some columns the way he wants them? Your code runs the risk of resetting them to the default column width. My idea was to compare the autofitted width of each column to the width of that column before autofit, and to restore the old width if the column has become narrower.

  9. #9
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Widen (#####) columns with code (Excel 2000 >)

    Ah! Of course you are correct.
    -John ... I float in liquid gardens
    UTC -7ąDS

  10. #10
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Widen (#####) columns with code (Excel 2000 >)

    If you want to check cell B1 to see if it is displaying #####, you can do the following:

    If Left(Range("B1").Text,1)) = "#" Then

    To check and AutoFit all of the cells in the current selection, you could do this:

    Dim oCell As Range
    For Each oCell In Selection
    If Left(Trim(oCell.Text), 1) = "#" Then
    oCell.EntireColumn.AutoFit
    End If
    Next oCell
    Legare Coleman

  11. #11
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Widen (#####) columns with code (Excel 2000 >)

    Thanx Hans for the suggestions!
    Thanx John for the effort and the code!

    It is perfect. BIG <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>
    Regards,
    Rudi

  12. #12
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Widen (#####) columns with code (Excel 2000 >)

    Thanx for the suggestion Legare. I tested your code and it works just as well.

    One question: Your method tests for each cell in the selection. (The selection might at certain times be up to 20 - 30 columns.) This may account for the loop running 1966080 times. When a column is autofitted, does it also test each cell internally in code? I suppose my question boils down to: Which code example is more efficient, or are they both ultimately the same? I ask simply for interest sake; I'm grateful to both you and John for helping out with the code! Many many thanx!
    Regards,
    Rudi

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

    Re: Widen (#####) columns with code (Excel 2000 >)

    AutoFit works on a column as a whole, not on a single cell in a column, so it is more efficient to loop through the columns than to loop through all cells.

  14. #14
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Widen (#####) columns with code (Excel 2000 >)

    Thanx. You don't need to answer this...but it makes me wonder what the code looks like that drives autofit? How is it possible to autofit a column based on the longest entry in a cell in the column without testing the iduvidual length of each cell. It must have to do with arrays... or your favourite UBound/LBound functions... <img src=/S/wink.gif border=0 alt=wink width=15 height=15>
    Regards,
    Rudi

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

    Re: Widen (#####) columns with code (Excel 2000 >)

    Of course the internal code used by Excel looks at each individual cell, but you as a programmer need not be concerned by that - for you, AutoFill operates on an entire column (or entire row).

Page 1 of 3 123 LastLast

Posting Permissions

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