Autofit in VBA

[Yamezz]

The last section of my macro tidies up the appearance of my worksheet, including the line

Columns("P:P").EntireColumn.AutoFit

This does not actually autofit column P around cell P1, which contains a currency value and is the only cell in column P that contains data.
If I use the macro recorder and manually double-click on the border of column P (at the completion of my maco), the column autofits correctly and the output of the macro recorder is

Columns("P:P").EntireColumn.autofit

Aside from the capital A and F, the code is identical. Why does my code not work, when it is the same code as the macro recorder?

 

[mrshl9898]

So double clicking produces a different result to the code? Can you share some screenshots?

 

[Logit]

What about : Columns("P").EntireColumn.AutoFit

 

[Yamezz]

This is the output of my macro:

This is the result when manually double clicking the column to AutoFit:

So, the line Columns("P:P").EntireColumn.AutoFit or the @Logit variation Columns("P").EntireColumn.AutoFit does not function correctly in my original macro, but if I use the following as a stand-alone macro on the result of my initial macro, it does work.

Sub AutoFit()
Columns("P").EntireColumn.AutoFit
End Sub

The surrounding code in my original macro is:

Range("A1:O" & Cells(Rows.Count, "A").End(xlUp).Row).SpecialCells(xlCellTypeVisible).Copy
Windows("Monthly Activities.xlsx").Activate
    Sheets.Add After:=Sheets(Sheets.Count)
    ActiveSheet.Name = "Vessels"
    ActiveSheet.Paste
    Range("P1") = StorageCost
    Range("A2:O2").Select
    Selection.AutoFilter
    Columns("A:P").EntireColumn.AutoFit
    Columns("P").EntireColumn.AutoFit
    Range("A1").Select

The line Columns("A:P").EntireColumn.AutoFit Correctly autofits columns A through O, but not P and the line Columns("P").EntireColumn.AutoFit does not autofit column P either.

 

[Logit]

Strange ... obviously there is some kind of corruption in Col P ?

Can you rebuild your workbook from the start ? Not copy and paste but actually rewrite it ?

 

[Yamezz]

Strange ... obviously there is some kind of corruption in Col P ?

Can you rebuild your workbook from the start ? Not copy and paste but actually rewrite it ?

I'm running a small contract winery and the workbook "Monthly Activites" is created for each client as a record of what happened to their wines each month and what quantities they have stored where and at what cost. The macro is basically filtering my master workbook for the records of a particular client and a particular month and copying it over to a simplified file for them. The master workbook is huge, with records from every day back as far as 17 years ago. No chance of re-writing that one, and given "Monthly Activities" is created on the fly, with no input directly from me, there's nothing to rewrite.