Messing Up on AutoFit

[John Quinn]

This is only the Fourth Time I ever worked with Excel, so please excuse my
ignorance.

I have four columns I am creating in a new WorkBook.

Two have very long descriptions and look terrible. I come from a huge
Worksheet so I am creating a more manageable new WorkBook.

My AutoFit is not working?

Any body know what I am doing wrong. Part of my exisiting code is from the
users group already.

Here is my code.

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

Set wkst = ActiveSheet

SelCol = InputBox("Comment Please:!")
ColNum = Columns(SelCol & ":" & SelCol).Column
SelCol = "A1," & SelCol & "1"

Set wb = Workbooks.Add(1)
Set wslb = wb.Worksheets(1)

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

wslb.Range("A1:E1").Columns.AutoFit * AutoFit Not Working

Thanks to the Group

J. Q.

 

[Barb Reinhardt]

What is the error message you are getting?

 

[John Quinn]

Barb;
Thanks for getting back to me.

I do not get an error message, it just does not work!

Thanks Again

J.Q.

 

[iCalculate]

This is only the Fourth Time I ever worked with Excel, so please excuse my
ignorance.

I have four columns I am creating in a new WorkBook.

Two have very long descriptions and look terrible. I come from a huge
Worksheet so I am creating a more manageable new WorkBook.

My AutoFit is not working?

Any body know what I am doing wrong. Part of my exisiting code is from the
users group already.

Here is my code.

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

Set wkst = ActiveSheet

SelCol = InputBox("Comment Please:!")
ColNum = Columns(SelCol & ":" & SelCol).Column
SelCol = "A1," & SelCol & "1"

Set wb = Workbooks.Add(1)
Set wslb = wb.Worksheets(1)

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

wslb.Range("A1:E1").Columns.AutoFit * AutoFit Not Working

Thanks to the Group

J. Q.

Hi,

maybe at the end try wslb.Columns("a:e").AutoFit instead of
wslb.Range("A1:E1").Columns.AutoFit

regards

 

[John Quinn]

Thanks for getting back to me!

Same results, no change and no halts!

J.Q.

 

[Dave Peterson]

If those columns are set to wrap text (format|Cells|alignment tab), then maybe
the autofit is working perfectly fine.

You may want to make sure that wrap text is off.

If you autofit the columns manually do you get a different result?

You could try something like:

with wslb.Range("A1:E1").entirecolumn
.columnwidth = 255
.autofit
end with

This won't look pretty if any of those columns are empty!

 

[John Quinn]

Dave;

That did the trick!

Thanks Again, that is the second time you solved a problem for me!

J.Q.