Column.Autofit not working

[Guinaba]

Hi guys,

Just wondering why Column.Autofit is not working in the code below which is copying and pasting special (values and format). I need to autofit the last col (12) in the range. Any suggestion?

Sub CopyWithFormatting()

'Clean sheet before pasting
Sheets("EmailFormat").Cells.Clear

Dim LastRow As Integer
    
With Worksheets("Email")
    LastRow = .Cells(.Rows.Count, "M").End(xlUp).Row
    Sheets("Email").Range("A3:M" & LastRow).Copy
    
    With Sheets("EmailFormat")
       Range("B2").PasteSpecial xlPasteValues
       Range("B2").PasteSpecial xlPasteFormats
        
       For r = LastRow To 2 Step -1
        If .Range("B" & r) = "N" Then .Range("B" & r).EntireRow.Delete
       Next r
        
    End With
End With

'Last Row after deleting the empty rows
Dim LastRow2 As Integer
With Worksheets("EmailFormat")
    LastRow2 = .Cells(.Rows.Count, "B").End(xlUp).Row
End With

'Formating the range
Dim ConRange As Range
Set ConRange = Worksheets("EmailFormat").Range("C2:N" & LastRow2)

Worksheets("EmailFormat").Columns(12).AutoFit

End Sub

 

[Joe4]

You don't not include the column header in your screen print, so we cannot tell which column is column 12.
But all those columns look autofit to me.

It also seems a little odd to me that you seem to be setting a range named "ConRange" near the end of your code, but don't seem to be doing anything with it.

 

[mohadin]

May be

Worksheets("EmailFormat"). Columns(12).EntireColumn.AutoFit

 

[Peter_SSs]

Could the data (or at least one item of data) that you have pasted in column 12 contain trailing spaces?

 

[Guinaba]

Thanks @Peter_SSs! Spot on! I've removed the trailing spaces and it is working now.

 

[Peter_SSs]

Cheers. Glad we could help.