How can I get the length of the longest string in a column (Excel)?
excel, vba
Solution
Record these steps as a macro in the cell you want to calculate the max length.
1) enter this formula
=MAX(LEN(A1:A4), 1) -- Edit for your range.
2) `press control-shift enter` (FormulaArray)
3) now stop recording macro
4) view the VBA. copy what you need to your VBA.
Should give you something like this:
Selection.FormulaArray = "=MAX(LEN(R[-10]C[1]:R[-1]C[1]))"
Problem
I have an Excel spreadsheet that I am writing out to a file. I am currently using the following method to reproduce the contents of each cell: ``` cell_contents = Right(Space(10) & Trim(Cells(iRow, iColumn)), 10) ``` However, if the contents of the cell are longer than 10 characters long (or however long I choose to specify), then I loose parts of the data. Is there a way to quickly and easily get the length of the longest string in a range of cells? I have seen people suggest using the following loop, but I was hoping that I might be able to do it without having to loop over all the cells: ``` For Each c In SourceRange.Cells if len(c) > b then b = len(c) Next c ```