VBA code help

Good Morning, I am working on a VBA code to autofit, I am using a dropdown list and some of the wording is to long, so i added autofit. The code is working fine when i press the play button, but i want it to change automatcially when I select. Pleaes see code I hope that someone can help me.

Option Explicit
Public Sub AutoFitAll()
Dim mw As Single
Dim cM As Range
Dim rng As Range
Dim cw As Double
Dim rwht As Double
Dim ar As Variant
Dim i As Integer

With Sheet2
    Application.ScreenUpdating = False
        ar = Array(“A52”, “A53”, “A54”, “A55”, “A56”, “A57”, “A58”, “A59”, “A60”, “A61”)
   
    For i = 0 To UBound(ar)
    On Error Resume Next
    Set rng = Range(Range(ar(i)).MergeArea.Address)
    rng.MergeCells = False
    cw = rng.Cells(1).ColumnWidth
    mw = 0
    For Each cM In rng
    cM.WrapText = True
    mw = cM.ColumnWidth + mw
    Next
    mw = mw + rng.Cells.Count * 0.66
    rng.Cells(1).ColumnWidth = mw
    rng.EntireRow.AutoFit
    rwht = rng.RowHeight
    rng.Cells(1).ColumnWidth = cw
    rng.MergeCells = True
    rng.RowHeight = rwht
    Next i
    Application.ScreenUpdating = True
End With
End Sub

By: Babb

Leave a Reply

Your email address will not be published. Required fields are marked *