Problem with Auto Fit Sub on open

[Guest]

I have the follwoign code to Sort a column on a worksheet and resize to
autofit but I keep getting run time error 1004. Any Advice?

Private Sub Workbook_Open()

Worksheets("Inquiry").Range("A2:B100").Sort _
Key1:=Worksheets("Inquiry").Range("A2"), _
Key2:=Worksheets("Inquiry").Range("B100")


Worksheets("Inquiry").Range("B2:B100").Rows.AutoFit


End Sub

 

[Zack Barresse]

You might want to use the EntireColumn property...

Worksheets("Inquiry").Range("B2:B100").EntireColumn.AutoFit

HTH

 

[paul.robinson]

Hi
works OK on my machine (Excel 2002). Is there more to the code? What
does the data in the range look like?
regards
Paul

 

[Guest]

try

Private Sub Workbook_Open()

With ThisWorkbook
.Worksheets("Inquiry").Range("A2:B100").Sort _
Key1:=.Worksheets("Inquiry").Range("A2"), _
Key2:=.Worksheets("Inquiry").Range("B100")


.Worksheets("Inquiry").Range("B2:B100").EntireRow.AutoFit
End With

End Sub

 

[Guest]

I found my problem, there are a couple of cells in the range that are merged.
Is there any way to take the merged cells into account. Maybe I can set them
to a predetermined size and autosize anything that isnt a merged cell?
Because of the sort I can never be sure which rows will contain the merged
cells. Thanks

 

[Zack Barresse]

Instead, how about looking for a way to un-merge the cells. They cause more
problems than they benefit.

 

[Guest]

I wish it were that easy. Due to the way we are using the cells to copy from
excel into another application simple fixes like changing the column width or
font size aren't available for me. The only way to get rid on the merged
cells is to figure out a way to increase the 409.5 height limit on a cell.

 

[Guest]

Autofit doesn't work with merged cells.


this code by Jim Rech

Sub AutoFitMergedCellRowHeight()
Dim CurrentRowHeight As Single, MergedCellRgWidth As Single
Dim CurrCell As Range
Dim ActiveCellWidth As Single, PossNewRowHeight As Single
If ActiveCell.MergeCells Then
With ActiveCell.MergeArea
If .Rows.Count = 1 And .WrapText = True Then
Application.ScreenUpdating = False
CurrentRowHeight = .RowHeight
ActiveCellWidth = ActiveCell.ColumnWidth
For Each CurrCell In Selection
MergedCellRgWidth = CurrCell.ColumnWidth + _
MergedCellRgWidth
Next
.MergeCells = False
.Cells(1).ColumnWidth = MergedCellRgWidth
.EntireRow.AutoFit
PossNewRowHeight = .RowHeight
.Cells(1).ColumnWidth = ActiveCellWidth
.MergeCells = True
.RowHeight = IIf(CurrentRowHeight > PossNewRowHeight, _
CurrentRowHeight, PossNewRowHeight)
End If
End With
End If
End Sub

may get you started. It is fairly specialized in what it handles, but you
might be able to incorporate it into you situation or use the method shown.
Obviously no guarantees since I have no idea what your data looks like.

 

[Guest]

Thanks, I also realized I'mnot even making it to the Sizing portion of my
macro. The code stops at the Sort portion due to the cells "merged cells" not
being "identically sized". I tried to size the rows and columns so before the
sort runs every cell in the range is the same height and width but it still
returns the "identically sized" error.

Private Sub Workbook_Open()

With ThisWorkbook

.Worksheets("Concern").Rows("2:100").RowHeight = 20
.Worksheets("Concern").Columns("A:B").ColumnWidth = 20

.Worksheets("Concern").Range("A2:B100").Sort _
Key1:=.Worksheets("Concern").Range("A2"), _
Key2:=.Worksheets("Concern").Range("B100")

End With

End Sub