vba 如何获取列（Excel）中最长字符串的长度？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/23678242/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How can I get the length of the longest string in a column (Excel)?
提问by tlewis3348
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:
我有一个要写入文件的 Excel 电子表格。我目前正在使用以下方法来重现每个单元格的内容：
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?
但是，如果单元格的内容长度超过 10 个字符（或者我选择指定的长度），那么我会丢失部分数据。有没有办法快速轻松地获得一系列单元格中最长字符串的长度？
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
回答by xQbert
Record these steps as a macro in the cell you want to calculate the max length.
在要计算最大长度的单元格中将这些步骤记录为宏。
1) enter this formula
1）输入这个公式
=MAX(LEN(A1:A4), 1) -- Edit for your range.
2) press control-shift enter(FormulaArray)
2) press control-shift enter(公式数组)
3) now stop recording macro
3) 现在停止录制宏
4) view the VBA. copy what you need to your VBA.
4）查看VBA。将您需要的内容复制到 VBA。
Should give you something like this:
应该给你这样的东西：
Selection.FormulaArray = "=MAX(LEN(R[-10]C[1]:R[-1]C[1]))"
回答by dedek
Press Alt-F11, insert new module, paste code bellow.
按Alt-F11，插入新模块，粘贴代码如下。
Public Function maxRangeLength(data As Range) As Integer
Dim ret As Integer
ret = 0
For Each cell In data
ret = Application.Max(ret, Len(cell))
Next cell
maxRangeLength = ret
End Function
Usage:
用法：
=maxRangeLength(A8:D11)
回答by Leadfingers
Suppose that the data is in column A and there is a heading row for your spreadsheet.
假设数据在 A 列中，并且电子表格有一个标题行。
- Create a new column next to it and enter in the formula: =LEN(A2)
- Autofill down that formula.
- Turn on filtering for that column and the click the drop-down box on the column heading cell.
- Scroll down to the bottom of the list where the largest summarized value is.
That will be the largest value in the column.
- 在它旁边创建一个新列并输入公式：=LEN(A2)
- 自动填充该公式。
- 打开该列的过滤，然后单击列标题单元格上的下拉框。
- 向下滚动到最大汇总值所在的列表底部。
这将是该列中的最大值。
