Need Help with AutoFit and Blank(Null) Cells

[GrandMaMa]

On the code below I create a New WorkBook. Everything works great except
that when I display my new WorkBook the Rows acn be anywhere from 3/8" to 2"
in height.

I use the .ColumnWidth = 111 line to get by my Blank (Null) Cells. I think
this is where my problem is, but I am not real sure. The 111 comes from the
fact that it is the largest number of characters that can be in any one cell
on the Application.


Sub ClassIntl()

Dim wkst As Worksheet ' Current Sheet
Dim lbls As Workbook
Dim wslb As Worksheet ' Work Sheet
Dim rng As Range
Dim lastRow As Long
Dim SelCol As String
Dim ColNum As Long
Dim HSClass As Long

Set wkst = ActiveSheet
HSClass = 12

SelCol = InputBox("Enter the Column for the registrar You Want
to Send Scores to:!")
ColNum = Columns(SelCol & ":" & SelCol).Column
SelCol = "A1," & SelCol & "1"

Set lbls = Workbooks.Add(1)
Set wslb = lbls.Worksheets(1)
lbls.Title = "Letters to Universities"
lbls.Subject = "IntlTest"

With wkst
If .AutoFilterMode Then .AutoFilterMode = False
Set rng = .Range("A1").CurrentRegion
With rng
.AutoFilter Field:=HSClass, Criteria1:="AAA"
.AutoFilter Field:=ColNum, Criteria1:="<>"
.Columns(ColNum).Copy wslb.Columns(1).Cells(1)
.Columns(2).Copy wslb.Columns(2).Cells(1)
.Columns(5).Copy wslb.Columns(3).Cells(1)
.Columns(7).Copy wslb.Columns(4).Cells(1)
End With
End With

With wslb.Range("A:F").EntireColumn
..ColumnWidth = 111
..AutoFit
End With

If wkst.AutoFilterMode Then wkst.AutoFilterMode = False

ActiveSheet.PrintPreview

wslb.SaveAs Filename:="C:\ExcelExp\JustNE.xls",
FileFormat:=xlNormal

End Sub

Any assistance in helping me make this new Book a little more representable
will be appreciated.

Thanks in Advance

Granny

 

[JLGWhiz]

There are a couple of things that can be causing the row height problem.
If you have word wrap on and you put data in the columns before you execute
the column width and autofit commands, it will increase the row height for
cells that exceed the column width. Row height will also adjust for font
size if it exceeds the standard height.

Just a passing comment, information that I have says a cell can hold and
display a lot more than 111 characters. You must mean that is your limit.

 

[OssieMac]

If you have wrapped text turned on and any of the rows are higher than normal
then when you widen the column to 111 to accommodate all the text on one line
and then run auto column width, only the width of the column adjusts to the
length of the text. The row height remains the same. Try running Row Height
also as per the example.


With wslb.Columns("A:F")
.ColumnWidth = 111
.AutoFit
.Rows.AutoFit
End With

Regards,

OssieMac

 

[Len]

Granny;
I do not know what version of Excel you have, but if it is earlier
than 2004 just insert one line!

With wslb.Range("A:F").EntireColumn
.ColumnWidth = 111
.AutoFit
.RowHeight = 14 ' This will make a perfect 5/8" Row Height for all of
your Rows!
End With

The Word Wray works much better in 2004 or 2007!

Good Luck

Len