Max Length of strings in cell range.
 0 0

shares
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.
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.