Find MAX not working...

9tanstaafl9

Well-known Member
Joined
Mar 23, 2008
Messages
535
Anybody know what I did rong with this?

Code:
Range("c:ca").Find(Application.WorksheetFunction.Max(Range("c:ca"))).Select

Honestly, I'd like to just use a named range, but that didn't work either. If I replace C:CA with A:A it works fine. I tried A:D and that worked fine, but when I try to go into the double letter alphabet columns it breaks (I don't know if that's what I'm doing wrong or if it's conincidental).

Really, all I'm trying to do is make all the columns in a named range the same width (the width of the widest of those columns in that range), using VBA. If someone knows an easy way to do that you can forget my original question...

Thanks!
Jennifer
 

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)
Is this what you want? just change the 10 to whatever you want




Sub SAMECOLWID()

' SAMECOLWID Macro

Columns("C:CA").ColumnWidth = 10
End Sub
 
Upvote 0
Almost. I'm don't know what the column width needs to be. During the running of my macro, a new set of data is brought in, and I need to set the column widths to all be exactly the same, and equal to a width wide enough so everything will fit. That's why I was trying to find the largest value in the range. I figured I could take that value and add 1 or 2 and it would be pretty safe.

Better would be to be able to find the actual column width of the widest column, but I've been searching all over the internet and can't figure out how to do that.
 
Upvote 0
Not quite. That would have all the columns autofit, but they would vary in width. I need all the columns to be the exact same width, and wide enough for the widest column. (They will all have different numbers.)
 
Upvote 0
What about this,

Code:
Columns("C:CA").ColumnWidth = Range("C:CA").Find(Evaluate("Max(C:CA)")).ColumnWidth

Regards
 
Upvote 0
What about this,

Code:
Columns("C:CA").ColumnWidth = Range("C:CA").Find(Evaluate("Max(C:CA)")).ColumnWidth

Regards
It looks like it should work, but it doesn't. I think there was a problemw with the quotes and parentheses, but I'm not sure I fixed it correctly. I changed it to this:

Code:
Columns("C:CA").ColumnWidth = Range("C:CA").Find(Evaluate(Max _("C:CA"))).ColumnWidth

But that returned the error :
Sub or Function not defined.

And it highlighted MAX.

Any suggestions? It seems like this would be an easy thing to do, but searching for it is impossible, since my terms I'm looking for are so common. Ok, maybe not impossible, but I'm not having any luck.

I tried adding RANGE too and that didn't work either.

Code:
Columns("C:CA").ColumnWidth = Range("C:CA").Find(Evaluate(Max(Range("C:CA")))).ColumnWidth
- same error
 
Last edited:
Upvote 0
NEVERMIND everyone. I was on the wrong track and don't need this info anymore.

If case anyone stumbles across this looking for a way to set the columns equal to the width of the widest column, I used this instead:

Code:
'be sure to name a range for the columns you want to use
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
 
Upvote 0
This works well:

Code:
Sub Macro1()

    Cells.EntireColumn.AutoFit
    ReDim maxWidth(1 To Range("RangeDrag").Columns.Count)
    y = 1
    For col = 1 To Range("RangeDrag").Columns.Count
        maxWidth(y) = Cells(1, col).ColumnWidth
        y = y + 1
    Next col
    i = WorksheetFunction.Max(maxWidth)
    Range("RangeDrag").ColumnWidth = i 
End Sub
 
Upvote 0

Forum statistics

Threads
1,213,551
Messages
6,114,273
Members
448,559
Latest member
MrPJ_Harper

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top