CODEWITHSUNDEEP
excelvba
jjrc
jjrc

Reputation: 69

How to loop through each column in Excel and apply column width based on condition

I am in need of excel vba code that will autofit all columns by default and then loop through each column width and if any width exceeds a particular value eg., 50, then restrict that particular column width to 30 and sets word wrap to true.

Public Function LastColumn(Optional wks As Worksheet) As Long
    If wks Is Nothing Then: Set wks = ActiveSheet
    LastColumn = Cells.Find("*", SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column
End Function


Sub Macro1()
    Dim LastCol As Long
    Cells.Select
    Cells.EntireColumn.AutoFit
    LastCol = LastColumn(ThisWorkbook.Sheets("Sheet1"))
    For i = 1 To LastCol
        If Columns(i).ColumnWidth > 70 Then
            Columns(i).ColumnWidth = 70
            Columns(i).WrapText = True
        End If
    Next i
End Sub

Is there a better way to achieve this?

Upvotes: 0

Views: 19912

Answers (1)

David Colwell
David Colwell

Reputation: 2590

Something like

Sub Autofit()

    Dim col As Range
    For Each col In ActiveSheet.UsedRange.Columns 'Only columns that actually have values
        col.Autofit
        If col.ColumnWidth > 50 Then 'Set your values here
            col.ColumnWidth = 30
            col.WrapText = True
        End If
    Next

End Sub

Note, this uses Excel widths, not Pixels

Upvotes: 2

Related Questions