Determine max number of characters in any cell in a column in excel, using VBA

Try this

col = 3
rw = 2000
Range("A1").FormulaArray = "=max(len(r1c" & col & ":R" & rw & "C" & col & "))"

Any way to do that without pushing the value to a cell first, just going straight to a variable:

intMaxColLen = ????

@ssaqibh

Unfortunately, you can't use worksheetfunctions, since the Len() function is not a worksheet function.

You can create a user defined function for this.

Option Explicit

Public Function MaxLen(parmRange As Range) As Long
    Dim strItem As String
    Dim lngMaxLen As Long
    Dim lngLen As Long
    Dim rngCell As Range
    lngMaxLen = Len(parmRange.Cells(1, 1).Value)
    For Each rngCell In parmRange
        lngLen = Len(rngCell.Value)
        If lngMaxLen < lngLen Then
            lngMaxLen = lngLen
        Next
    Next
    MaxLen = lngMaxLen
End Function

Select allOpen in new window

You can get even more performance if you copy the range into a variant array and iterate the array.  This is the inverse of the technique I showed in my Fast Data Push to Excel article.
https://www.experts-exchange.com/A_2253.html

LEN is a perfectly valid worksheet function, so saqibh's approach will definitely work.

Rather than troll through an entire column of cells, it is more efficient to restrict it to the used range. I modified aikimark's function to do just that, adding in his suggestion of copying the range into a variant.

Public Function MaxLen(parmRange As Range) As Long
    Dim lngLen As Long
    Dim v As Variant, vData As Variant
    vData = Intersect(parmRange.Worksheet.UsedRange, parmRange).Value
    For Each v In vData
        If Len(v) > lngLen Then lngLen = Len(v)
    Next
    MaxLen = lngLen
End Function

Select allOpen in new window

Not very elegant but to avoid the loop I would have tried something like

col = 3
rw = 20000
tmp = Cells(1, col + 1).FormulaR1C1
Cells(1, col + 1).FormulaArray = "=max(len(r1c" & col & ":R" & rw & "C" & col & "))"
intMaxColLen = Cells(1, col + 1).Value
Cells(1, col + 1).FormulaR1C1 = tmp

when I wrote that you can't use worksheet functions, I meant that in the context of a VBA routine.  There is certainly a Len() function that can be used in a formula.

Rory,
I get a type mismatch with your code in Excel 2013 whenever there are more than 255 cells being tested. Your sub works when there are 255 or fewer cells being tested.

Brad

Brad,

I can't reproduce that. Just ran a quick test with 800 odd cells populated in a column with no problems. Can you provide a workbook?

Rory

Rory,
The type mismatch occurred in Excel 2013 when I ran your macro from the Immediate pane command line. But when I ran it from a command button on the worksheet in response to your Comment requesting a workbook, your code worked. Hmm...

So I rebooted my computer (both Mac and Parallels), and now your code works both on the Immediate pane command line and when called from a worksheet command button. It also works when I turn your one-liner into a UDF called from a worksheet formula.

My computer must have had some corruption that was cured by rebooting.

I apologize for the false accusation.

Brad

No worries. :)

This worked well, since I already had the workbook open and knew how many rows to include in the string.

Thanks, Rory.