Can this code be streamlined with less "with" "End With" statements>?

Question

I ask as it seems there is a better way than having a separate "With" "EndWith for each Column.

On the active sheet For the entire Columns A1:C1 it is setting to autofit plus two blank spaces, and for the entire Columns D1:G1 it is setting to autofit plus one blank space.

Sub AutoFitColumns()
Worksheets("TestSheet1").Activate

    With Range("A1").EntireColumn
        .AutoFit
        .ColumnWidth = .ColumnWidth + 2
    End With
    With Range("B1").EntireColumn
        .AutoFit
        .ColumnWidth = .ColumnWidth + 2
    End With
    With Range("C1").EntireColumn
        .AutoFit
        .ColumnWidth = .ColumnWidth + 2
    End With
    With Range("D1").EntireColumn
        .AutoFit
        .ColumnWidth = .ColumnWidth + 1
    End With
    With Range("E1").EntireColumn
        .AutoFit
        .ColumnWidth = .ColumnWidth + 1
    End With
    With Range("F1").EntireColumn
        .AutoFit
        .ColumnWidth = .ColumnWidth + 1
    End With
    With Range("G1").EntireColumn
        .AutoFit
        .ColumnWidth = .ColumnWidth + 1
    End With
End Sub

Tried this before coming up with the code above. It sets the colums to autofit but ignore the +1 and +2 characters. — user2303407, Feb 24 '23 at 13:07

Tim Williams · Answer 1 · 2023-02-24T02:22:11.407

3

For example:

Sub AutoFitColumns()
    Dim c As Range
    For Each c In Worksheets("TestSheet1").Range("A1:G1").Cells
        With c.EntireColumn
            .AutoFit
            .ColumnWidth =  .ColumnWidth + IIf(c.Column <= 3, 2, 1)
        End With
    Next c
End Sub

edited Feb 24 '23 at 02:22

answered Feb 24 '23 at 00:53

Tim Williams

154,628
8
97
125

For anyone else needing to do the same thing, Tims answer here works perfectly. – user2303407 Feb 24 '23 at 13:24
Mighty elegant! – VBasic2008 Feb 24 '23 at 13:31

score 0 · Answer 2 · answered Feb 24 '23 at 13:11

Autofit Columns

Sub AutoFitColumns()

    Dim AutoFitProperties()
    AutoFitProperties = Array(VBA.Array("A1:C1", 2), VBA.Array("D1:G1", 1))

    With ThisWorkbook.Sheets("TestSheet1")
        Dim Afp, crg As Range
        For Each Afp In AutoFitProperties
            With .Range(Afp(0)).EntireColumn
                .AutoFit
                For Each crg In .Columns
                    crg.ColumnWidth = crg.ColumnWidth + Afp(1)
                Next crg
            End With
        Next Afp
    End With

End Sub

Can this code be streamlined with less "with" "End With" statements>?

2 Answers2

Autofit Columns