Results 1 to 4 of 4
  1. #1
    2 Star Lounger
    Join Date
    Jul 2001
    Posts
    119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Size rows/columns (97)

    HI i am using a combination of code i received which looks like

    Sub TopAlign() ' aligns the text

    Cells.Select
    With Selection
    .VerticalAlignment = xlTop
    .Orientation = 0
    .ShrinkToFit = False
    .MergeCells = False
    .WrapText = True
    End With
    Range("A2").Select
    End Sub

    Sub ChooseAll() ' selects all the cells
    ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell) .Select
    Range(ActiveCell, "a1").Select
    End Sub


    Sub AutoSize() ' sets the size of the rows/columns ////problem in here

    TopAlign

    Columns("E:E").Select ' these 2 columns i want fixed widths for since they tend to get large
    Selection.ColumnWidth = 42
    Columns("F:F").Select
    Selection.ColumnWidth = 42

    ChooseAll
    With Selection ' I would then like to aut fit the other remaining columns and rows to get my
    ' format correct.

    .EntireRow.Select
    .Columns.AutoFit

    .EntireColumn.Select
    .Rows.AutoFit
    End With
    End Sub


    What happens is the 2 columns get set at 42 which is fine but the autofit of the rows still cuts off data when it is very long. So in essence itsnot really autosizing my rows. How can i manually set 2 column widths and then autosize the rest of my spreadsheet?

    Thanks

  2. #2
    3 Star Lounger
    Join Date
    Mar 2001
    Location
    Silicon Valley, California, USA
    Posts
    273
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Size rows/columns (97)

    Hi,

    First, here is a simpler version of your routine for setting the column width of the two specific columns:

    <pre>Columns("E:F").ColumnWidth = 42
    </pre>


    Next, if you auto fit a row, that means automatically set the row height (not the column width). Row heights tend to automatically expand when needed anyway, like when you have a cell with Wrap Text turned on and you type something longer than the width. Typically, you only need to AutoFit a row height if it was accidentally manually set to some row height that is too small.

    When you say "still cuts off data when it is very long" do you mean, the text goes past the end of the cell and gets covered up by (or bleeds into) the next cell to the right? If so, you can either 1) fix the width of that column, specify Wrap Text for the cells in question and AutoFit the row height, or 2) AutoFit the column width (in which case, the column width will widen until all the text fits). In case 2, if Wrap Text is also specified, AutoFit of the column width will shrink the column width to fit the widest section of text, without altering where the Wrap Text "breaks" are located.

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

    Re: Size rows/columns (97)

    Try the code below:

    <pre>Sub TopAlign() ' aligns the text
    With ActiveSheet.Cells
    .VerticalAlignment = xlTop
    .Orientation = 0
    .ShrinkToFit = False
    .MergeCells = False
    .WrapText = True
    End With
    Range("A2").Select
    End Sub


    Sub AutoSize() ' sets the size of the rows/columns ////problem in here

    TopAlign

    Range("A1", ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell) ).Columns.AutoFit
    Range("A1", ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell) ).Rows.AutoFit
    ActiveSheet.Range("E1:F1").EntireColumn.ColumnWidt h = 42
    End Sub
    </pre>


    I'm not sure exactly what you are trying to do, so this code may not be exactly what you need. You are setting the WrapText property for the entire sheet to true befoe you use the auto fit method. This is going to keep the autofit from doing much. If WrapText is set to true, then AutoFit will not change the cell width or height to fit the already wrapped text. You may want to set the WrapText property to False for the entire sheet as the first thing in the AutoSize routine and then call TopAlign after the AutoFit is done.
    Legare Coleman

  4. #4
    2 Star Lounger
    Join Date
    Jul 2001
    Posts
    119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Size rows/columns (97)

    You were right as usual Legare, the wrap text was already doing what i needed. I decided to manually set all my cloumn widths and then just wrap the text. got rid of all the autofitting, how simple life may be. Thank you.

Posting Permissions

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