none
A resizing column width macro produces incorrect results. . . sometimes. RRS feed

  • Question

  • I've taken steps to resize the column widths and sometimes row heights from earlier version of spreadsheets created in 2007 to adjust them proportionally to our new 16:9 screens. Whenever we open most existing spreadsheets now, the columns want to adjust somehow to extra widths,  So I've recorded a macro changing the column widths to an even value, whereas the new widths have something additional like 11.18 whereas previously they might have been an even 10 points.  After changing the widths in the VB editor, then when stepping thru, it changes the widths of all columns to whatever code line it is on, messing up the whole sheet, and ending with the last value of 2.

    Here is my code:

    Sub ReSizeSumColsToNewScreenSize()
    '
        Columns("A:A").Select
        Selection.ColumnWidth = 2
        Columns("B:B").Select
        Selection.ColumnWidth = 12
        Columns("C:D").Select
        Selection.ColumnWidth = 12
        Columns("E:E").Select
        Selection.ColumnWidth = 2
        Columns("F:F").Select
        Selection.ColumnWidth = 12
        Columns("G:G").Select
        Selection.ColumnWidth = 2
        Columns("H:H").Select
        Selection.ColumnWidth = 12
        Columns("I:I").Select
        Selection.ColumnWidth = 2
        Columns("J:J").Select
        Selection.ColumnWidth = 9
        Columns("K:K").Select
        Selection.ColumnWidth = 1
        Columns("L:L").Select
        Selection.ColumnWidth = 12
        Columns("M:M").Select
        Selection.ColumnWidth = 2
        Columns("N:O").Select
        Selection.ColumnWidth = 9
        Columns("P:P").Select
        Selection.ColumnWidth = 1
        Columns("Q:Q").Select

    Seems like it can't get much simpler than this so what gives?  I've had this problem with Excel 2003 also

    but the results are not always consistent. It seems like a glitch.  Any ideas?

    TIA


    Roliver

    Wednesday, May 7, 2014 8:25 PM

All replies

  • Re:  column widths

     See if this works for you...

     Sub MakeThemNice()
       Columns("A:Q").AutoFit
     End Sub
    '---
    Jim Cone
    Portland, Oregon USA
    https://goo.gl/IUQUN2 (Dropbox)
    (free & commercial excel add-ins & workbooks)

    • Edited by James Cone Sunday, October 30, 2016 11:08 PM
    Wednesday, May 7, 2014 10:24 PM
  • That would work but then it wouldn't fit the need of being able to print all sheets to the same aspect and proportions, regardless of how much data exists in each cell.  I really need a better solution.

    Roliver

    Thursday, May 8, 2014 3:54 PM
  • Re: you should have read my mind he said

    Sub MakeColumnsSame()
    'Jim Cone - May 2014
    Dim arrWidths As Variant
    Dim WS As Excel.Worksheet
    Dim N As Long

    arrWidths = Array(2, 12, 12, 12, 2, 12, 2, 12, 2, 9, 1, 12, 2, 2, 9, 1, 12)
    For Each WS In ThisWorkbook.Worksheets
      For N = LBound(arrWidths) To UBound(arrWidths)
        WS.Columns(N + 1).ColumnWidth = arrWidths(N)
      Next
    Next 
    End Sub
    '---
    Jim Cone

    Thursday, May 8, 2014 5:15 PM
  • Jim,

    This seems logical but effects the worksheet naught one bit. . . not even on a blank worksheet. So, after running the first version I now have a corrupted spreadsheet with all columns with a column width of 2. I need a macro that will only affect all activesheets and not all sheets because each workbook has multiple sheets with two different format widths styles. FWIW, I'm really curious as to why my code doesn't work any longer, especially since its from a recorded macro with minimal editing. I've never understood why it works sometimes on older sheets and other times it corrupts them as above.  Not all sheets have this display problem so I usually need to select them one or two at a time, then run this macro.  I have two different macros, one for each of the two different formatted styles of worksheets.   


    Roliver

    Thursday, May 8, 2014 10:54 PM
  • Re: "I need a macro that will only affect all activesheets"
    There is only one active sheet in Excel versions released prior to xl2013.

    Re:  "curious as to why my code doesn't work any longer,"
    I don't know... corrupt workbook?, code not in a standard module?, protected sheets?

    What happens if you open a brand new workbook, place the code in a standard module and run it?
    '---
    Jim Cone

    Friday, May 9, 2014 12:28 AM
  • Well, starting from a blank new workbook, it runs like it should, changing width on one or more columns at a time.  The same code in an existing book changes all the column widths A thru U to each width for the line of code being executed.  I have no clue as to why. . . I sure could use a way to fix that. Any settings I could change, add, etc.?

    Roliver

    Friday, May 9, 2014 8:31 PM
  • Re: new workbook vs. existing workbook

    Chances are the workbook is corrupt.
    I'm willing to take a look at it, but
    I don't want to see it if it contains proprietary data or personal information of any sort.
    You could post it on a public website and paste a link to it in this forum
    That would also give others the chance to examine it.
    '---
    Jim Cone

    Friday, May 9, 2014 9:40 PM