Find widest column in range?

[9tanstaafl9]

How would I go about finding the widest column in a range using VBA?

I'm trying to make all the columns equal in width, and as wide as the widest column. I've got the part to adjust the column widths figured out, I just need to know how to find the one that is widest in the first place so I can steal it's columnwidth for my macro. needs to be done in VBA since it will change every time the report is run.

Thanks!

And if this is cross posting I apologize. I'm not sure since the question no longer has to do with the function I asked about. I had asked a question earlier about using MAX and it not working correctly. Someone helped me with that, and I got MAX to work, but it didn't do what I thought it would do and i think I was on the totally wrong track.

 

[Weaver]

Try:

    Dim maxWidth As Long
    For Each col In ActiveSheet.UsedRange.Columns
        If col.Width > maxWidth Then
            maxWidth = col.Width
        End If
    Next

 

[9tanstaafl9]

Thank you! This has been bugging me for the last two days!

 

[9tanstaafl9]

Try:

    Dim maxWidth As Long
    For Each col In ActiveSheet.UsedRange.Columns
        If col.Width > maxWidth Then
            maxWidth = col.Width
        End If
    Next

Thank you! This had been bugging me for two days. I had to modify it a little:

Range("RangeDrag").Columns.AutoFit
    Dim maxWidth As Long, Col As Variant
    maxWidth = 0
    For Each Col In Range("rangedrag").Columns
        If Col.ColumnWidth > maxWidth Then
            maxWidth = Col.ColumnWidth
        End If
    Next
 
Range("RangeDrag").ColumnWidth = maxWidth