Max Length of strings in cell range.

When transferring tabular data from excel or csv into an SQL table you can run into truncation issues, this is usually due to SQL's importer only testing a few rows and predicting max field length, which can throw errors later on in the script.

For small ( less than 1m lines ) tables, I made a quick function that will loop through all cells (regardless of data type ) and return the maximum string length of the included range.

There are no additional references needed, so this can just be copied/pasted into a module.

This can then be used in Other VB scripts, or as a cell formula.

Function MaxLenRange(RNG As Range) As Integer

Dim C As Range, MyLength As Integer
MyLength = 0 'Set Length Measure to Zero.

For Each C In RNG.Cells 'Loop through each cell in range and get string Length.
 
 If Len(C.Value) > MyLength Then 'If Cell's string length is greater than
 MyLength = Len(C.Value) 'current 'Max' then update variable to new Length.
 End If
 
 Next C
 
MaxLenRange = MyLength ' Return Max Length to Function Variable.
End Function

You can test it by creating two columns, in column B, is a random integer number :

=INT(RAND()*25)

Then in the second column ( column C ), referencing the first ( column B ) :

=REPT("Z",B2)

This will create a column of random length strings to test the function with.

As you can see, the max numeric value of B is the same as the MaxLenRange function result.

Go to the formula tab and hit 'Calculate Now' to test it.