Calculate the Max Field Length 2

[V00D00]

I am looking for a way to calculate the longest field length in use for a given column in excel. A formula or VBA solution would be most greatly appreciated.

VD

 

[PHV]

Brute force method in VBA:
longest = 0
For Each c In yourcolumnRange
l = Len(Trim(c.Text))
If l > longest Then longest = l
Next
MsgBox "longest = " & longest

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[GlennUK]

Formula method:

=MAX(LEN(A2:A65536))

entered using Ctrl-Shift-Enter.

Note: the length of the range is 1 less than the total number of cells in a column. This had been done on purpose, as in most versions of Excel having all cells in a column in a formula like this will result in the #NUM! error.

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.