autofit columns BUT for columnwidth to not exceed 40 ?

[shell_l_d]

I'd like to autofit columns but not allow the column width to exceed 40 (as some may be 255)... any ideas please?

Here's an extract of what I have now:

    Dim lastCol As Integer
    Dim lastRow As Integer
    
    With ActiveSheet
        
        ' find position of entire table including headings
        lastCol = .Range("A1").End(xlToRight).Column
        lastRow = .Range("A1").End(xlDown).Row
        endTable = .Cells(lastRow, lastCol).Address
        
        ' Resize rows & columns
        '.Cells.EntireRow.AutoFit
        Rows("1:" & lastRow).RowHeight = 15
        'Columns("A:E").ColumnWidth = 40
        .Cells.EntireColumn.AutoFit
    end with

 

[Jon von der Heyden]

Try something like:

Dim rngCol As Range
With Columns("A:E")
    .AutoFit
    For Each rngCol In .Columns
        If rngCol.ColumnWidth > 40 Then rngCol.ColumnWidth = 40
    Next rngCol
End With

 

[shell_l_d]

Thanks Jon... will give that a shot (i need to use it for a defined table).
having problems with missing library
("MISSING: Microsoft Windows Common Controls-2 6.0 (SP6)")
at moment for different PC's... so trying to fix that first (auto install option).

 

[shell_l_d]

Trying to get this to work... or similar:
Want to do following:
* autofit columns (works fine)
* select my (dynamically) defined table (works fine)
* set a maximum column width for every column in the selection (struggling) (
eg: if current columnwidth > 40 then set columnwidth = 40

' select a dynamically sized table
Range("tblGlobalData[#Headers]").Select

' loop through each column? apparently loops should be avoided though
' if columnwidth > 40 then 
'     set columnwidth = 40

' works fine if hardcode for ONE hardcoded column only
'Columns(1).ColumnWidth = 20
'Range("B1").ColumnWidth = 30
    
' Found this code in another MrExcel thread
'If Columns(Col & ":" & Col).Width > 528.75 Then
'    Columns(Col).ColumnWidth = 528.75
'End If

Think I'm starting to go crosseye'd looking at nothing but VB code for the last 5 days straight...

 

[Jon von der Heyden]

I don't quite see how you would avoid a loop for this, but the order should be:

Select the range / columns.
AutoFit the columns
Loop through the columns.
If the column width is greater than desired width Then
Resize the column width to the desired width

 

[shell_l_d]

Ended up doing this (lastColRef)... thanks John for your help...

    Dim lastCol As Integer, lastRow As Integer, x as Integer
    Dim endTable As String, lastColRef As String, tblName As String
    Dim rngCol As Object
    
    With ActiveSheet
        
        ' find position of entire table including headings
        lastCol = .Range("A1").End(xlToRight).Column
        lastRow = .Range("A1").End(xlDown).row
        endTable = .Cells(lastRow, lastCol).Address

        ' extract the column letter from endTable  eg: AB from "$AB$100"
        x = InStrRev(endTable, "$", -1)
        lastColRef = Mid(endTable, 2, x - 2)
    
        ' Resize rows & columns
        .Rows("1:" & lastRow).RowHeight = 15
        .Cells.EntireColumn.AutoFit
        With Columns("A:" & lastColRef)
            For Each rngCol In .Columns
                If rngCol.ColumnWidth > 35 Then
                    rngCol.ColumnWidth = 35
                End If
            Next
        End With

    End With