I am using the code below to autofit the columns on my spreadsheet.

Private Sub Worksheet_Change(ByVal Target As Range)

Me.Application.ActiveWorkbook.RefreshAll
Target.EntireColumn.AutoFit
End Sub

This works fine normally but I have 13 columns on this spreadsheet that
contain Hyperion Retrieve Formula. The values in these columns update when
either D66 or D68 are changed but the column wide is not updating with this.
I end up with the columns either too wide where the value has reduced, or
filled with #### where the number of digits has increased.

Any suggestions how I can resolve this? Help with the code would be great as
I'm just finding my feet with VBA.

Karen,

Try:


Private Sub Worksheet_Change(ByVal Target As Range)
Me.Application.ActiveWorkbook.RefreshAll
Target.EntireColumn.AutoFit

On Error Resume Next
Target.Dependents.EntireColumn.AutoFit
End Sub

--
Hope that helps.

Vergel Adriano


"Karen McKenzie" wrote:

> I am using the code below to autofit the columns on my spreadsheet.
>
> Private Sub Worksheet_Change(ByVal Target As Range)
>
> Me.Application.ActiveWorkbook.RefreshAll
> Target.EntireColumn.AutoFit
> End Sub
>
> This works fine normally but I have 13 columns on this spreadsheet that
> contain Hyperion Retrieve Formula. The values in these columns update when
> either D66 or D68 are changed but the column wide is not updating with this.
> I end up with the columns either too wide where the value has reduced, or
> filled with #### where the number of digits has increased.
>
> Any suggestions how I can resolve this? Help with the code would be great as
> I'm just finding my feet with VBA.

That works brilliantly!

Thanks for your help

"Vergel Adriano" wrote:

> Karen,
>
> Try:
>
>
> Private Sub Worksheet_Change(ByVal Target As Range)
> Me.Application.ActiveWorkbook.RefreshAll
> Target.EntireColumn.AutoFit
>
> On Error Resume Next
> Target.Dependents.EntireColumn.AutoFit
> End Sub
>
> --
> Hope that helps.
>
> Vergel Adriano
>
>
> "Karen McKenzie" wrote:
>
> > I am using the code below to autofit the columns on my spreadsheet.
> >
> > Private Sub Worksheet_Change(ByVal Target As Range)
> >
> > Me.Application.ActiveWorkbook.RefreshAll
> > Target.EntireColumn.AutoFit
> > End Sub
> >
> > This works fine normally but I have 13 columns on this spreadsheet that
> > contain Hyperion Retrieve Formula. The values in these columns update when
> > either D66 or D68 are changed but the column wide is not updating with this.
> > I end up with the columns either too wide where the value has reduced, or
> > filled with #### where the number of digits has increased.
> >
> > Any suggestions how I can resolve this? Help with the code would be great as
> > I'm just finding my feet with VBA.