Size rows/columns (97)

Topic

New Reply

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

Reply | Quote

Replies

Hi,

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

Columns("E:F").ColumnWidth = 42

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.

Reply | Quote

Try the code below:

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.ColumnWidth = 42
End Sub

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.

Reply | Quote

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.

Reply | Quote