我觉得使用一个临时表来检查临时列宽比循环要容易。

Sub limitWidthColW()
    Dim lRow As Long, rng As Range
    Dim ws As Worksheet, ws2 As Worksheet
    
    Set ws = Me.Worksheets("Sheet1") 'change your sheet, "Me" = ThisWorkbook, also change as needed
    lRow = ws.Range("A" & Rows.Count).End(xlUp).Row
    If ws.FilterMode Then '**
        Set ws2 = Me.Worksheets.Add(After:=Me.Worksheets(Me.Worksheets.Count))
        Set rng = ws.Range("W1:W" & lRow).SpecialCells(xlCellTypeVisible)
        
        rng.Copy ws2.Range("A1")
        ws2.Range("A1").EntireColumn.AutoFit
        Dim colWidth2 As Long
        colWidth2 = ws2.Range("A2").EntireColumn.ColumnWidth
        If colWidth2 > 50 Then colWidth2 = 50 'bonus!
        ws.Range("W2").EntireColumn.ColumnWidth = colWidth2
        'Cleaning up
        Application.DisplayAlerts = False
            ws2.Delete
        Application.DisplayAlerts = True
    Else '**
        ws.Columns("W:W").AutoFit '**
    End If '**
End Sub

如果未应用过滤器，这也会重置列W的自动调整，而不会创建/删除临时图纸。如果你不想发生这种情况，请删除“**"行。

AutoFit将始终使用最宽的文本设置列宽，无论单元格是否可见。
最好的办法是只将可见的单元格复制到（隐藏的）工作表中，在该列上使用“自动调整”，并将计算出的列宽分配给列。下面的例程将执行此操作，它假设工作簿中有一个名为“tmp”的工作表，并且数据。如果需要，可以传递可选的最小或最大列宽。

Sub AutofitVisible(r As Range, Optional minWidth As Double = 0, Optional maxWidth As Double = 0)
    Dim tmpSheet As Worksheet
    Set tmpSheet = ThisWorkbook.Sheets("Tmp")
    tmpSheet.UsedRange.Clear
    ' Copy only the visible cells
    r.SpecialCells(xlCellTypeVisible).Copy
    With tmpSheet.Range("A1")
        .PasteSpecial xlPasteAll  ' Copy the visible cells (with formatting)
        .EntireColumn.AutoFit     ' Autofit that data
        Dim colWidth As Double    
        colWidth = .ColumnWidth
        If minWidth > 0 And minWidth > colWidth Then colWidth = minWidth
        If maxWidth > 0 And maxWidth < colWidth Then colWidth = maxWidth
    
        r.EntireColumn.ColumnWidth = colWidth ' Set column width of orig. data
    End With
End Sub

调用看起来像这样：

AutofitVisible  Range("W4:W1400"), maxWidth := 50