Auto width columns under all circumstances

[Caulerpa]

Hello!

I'm trying to find a way to auto size column widths under all circumstances. The current method I'm using is:

Right-click worksheet tab --> View Code --> Paste:

Private Sub Worksheet_Change(ByVal Target As Range)
Target.EntireColumn.AutoFit
End Sub


This works wonderfully for the column I'm entering data in, but I have cells in other columns that reference data in the column I'm entering numbers into and it doesn't resize.

For example. I'm entering numbers into columns A through Y. In row 100 i've got sum formulas for all of the columns. In row Z I have a cell that takes the SUM of all of the sums in other rows. When I enter numbers into column A only column A adjusts it's size - not Z.

Is there a solution for this? Thank you so much!

[tom1977]

Hi try this

Please Login or Register  to view this content.

[Richard Buttrey]

Hi,

Are you saying that you want all columns resized to accomodate the maximum width in each of the columns? You're using the 'target' in the Worksheet change event which of course will only resize the column containing the cell.

You could try

Range("A1:Z1").EntireColumn.Autofit

instead.

Incidentally your location is some of our business. It's often very useful when we need to understand your likely system locale settings for say date arithmetic. e.g. US formats are quite different to UK formats.
Please change it to something meaningful.