查找显示内容太小的文本单元格 [英] Find Text Cells That Are Too Small to Display Contents
问题描述
识别哪个列太宽将是棘手的，特别是因为单元格内的文本可以有不同的字体和/或字体大小。通过使用 Range.AutoFit 方法。
例如，您可以按照以下方式调用AutoFit方法：
Dim myRange As Excel.Range
设置myRange = Application.Range（A1：C3）
myRange.Columns.AutoFit
Range.AutoFit方法可能有点太积极，但是导致的列太窄。因此，您可能需要创建一个建立最小列宽度的方法：
Sub AutoFitColumns（TheRange As Excel.Range，minColumnWidth As Double）
theRange.Columns.AutoFit
Dim列作为Excel.Range
对于每列在TheRange.Columns
如果column.ColumnWidth< ; minColumnWidth然后
column.ColumnWidth = minColumnWidth
结束If
下一列
End Sub
以上可以调用如下，自动填充列，但最小宽度为8.5：
Dim myRange As Excel.Range
设置myRange = Application.Range（A1：C3）
调用AutoFitColumns（myRange，8.5）
您还可以自动调整范围的行。这个需要不那么频繁，但要做到这一点，您可以使用以下内容：
myRange.Rows.AutoFit
希望这有帮助！
更新：回复到Craig的评论：
Thansk Mike。此电子表格使用
作为网络界面
生成器的结构输入，因此列宽度不同于
，不应进行调整。我真的是
只是寻找一些东西扫描一个
表单，其中文本
显示的宽度大于单元格
将允许适合的单元格，从而需要
行高大小较大。我不是
寻找一个过程来使
调整，只是找到它们，因为他们
容易忽视。任何关于
的想法？
很明显，你想要做两个的事情：（ 1）确定要切断的单元格，然后（2）更正这些截止值。
我明白你的愿望是在两个不同的阶段进行，但是1）几乎不可能在Excel的所有情况下正确地执行，因为不仅行列高度和列宽都可以不同，而且可以打开或关闭文本包装，并且字体可以是任意数量的潜在样式中的非比例字体， /或大小。
总之，没有办法可靠地确定哪些单元格被切断，而不是没有令人难以置信的工作量。但是，如果将电子表格限制为仅一种字体样式并使用非比例字体，则可以减轻它。如果这样做，那么您可以简单地将列宽与单元格内的文本长度进行比较。这是因为 Excel.Range.ColumnWidth 属性返回其校准的宽度，使得一个单位的列宽等于普通样式中一个字符的宽度。对于比例字体，使用字符0（零）的宽度。
因此，对于简单情况，不使用单词换行并且字体处于正常样式，理想情况下，正常字体是非比例字体，那么您可以循环遍历范围内的所有单元格，寻找持有值超过列宽度的位置：
Dim myRange As Range
设置myRange = Application.Range（A1：E5）
Dim单元格为Excel.Range
对于myRange.Cells中的每个单元格
如果Len（CStr（cell.Value））> cell.ColumnWidth然后
MsgBox在& cell.Address& 文字太长了！
如果
结束下一个单元格
但现在这里是下一部分..你怎么纠正这个？如果您再次使用非常简单的情况，即不使用单词换行，并且字体处于正常样式，而且理想情况下，正常字体是非比例字体，那么您可以选择几个选项：
（1）设置列宽以匹配单元格的值长度：
Dim myRange As Range
设置myRange = Application.Range（A1：E5）
Dim cell As Excel.Range
对于myRange.Cells中的每个单元格
如果Len（CStr（cell.Value））> cell.ColumnWidth然后
cell.ColumnWidth = Len（CStr（cell.Value））
如果
结束下一个单元格
（2）自动调整列：
Dim myRange As Range
设置myRange = Application.Range（A1：E5）
Dim cell As Excel.Range
对于myRange.Cells中的每个单元格
如果Len（CStr（ cell.Value））> cell.ColumnWidth然后
cell.Columns.AutoFit
如果
结束下一个单元格
$ b $但是，如果我们要自动调整列，那么直接调用它就更容易了，而不是首先检查列宽，因为不仅一次可以更正所有单元格的宽度，而且 Range.AutoFit 方法可以处理任何字体，包括非比例字体，任何风格。
因此，直接进入自动适应方法，我们有两个选项：自动调整列或自动调整行。根据你最近的报价，听起来你想要重新调整行：
m真的
只是寻找一些东西扫描一个
表单，其中文本
显示的宽度大于单元格
将允许适合的单元格，从而需要
行高度大。
为此，我将使用文本包装，然后自动填充行。例如，
Dim rng As Excel.Range
设置rng = Application.Range（A1：E5）
带rng.Rows
.WrapText = True
.AutoFit
结束
我认为这些都是你所有的选择。最后，你想做什么和Excel有能力做什么可能不完全相符，但我认为你应该能够完成你所需要的？让我们知道如果它是诀窍...
- Mike
I have a worksheet with cells that may contain text that sometimes exceeds the width and height of the cell. If I don't notice it and adjust the row height, the text shows up as cutoff. Does anyone have a VBA snippet or tool that can locate these cells so I can adjust them? Thanks!
Identifying which column is too wide would be tricky, especially since the text within the cell can have different fonts and/or font sizes. It is much easier to automatically size the columns by making use of the Range.AutoFit method.
As an example, you can call the AutoFit method as follows:
Dim myRange As Excel.Range
Set myRange = Application.Range("A1:C3")
myRange.Columns.AutoFit
The Range.AutoFit method can be a bit too aggressive, however, resulting in columns that are too narrow. Therefore, you might want to create a method that establishes a minimum column width:
Sub AutoFitColumns(theRange As Excel.Range, minColumnWidth As Double)
theRange.Columns.AutoFit
Dim column As Excel.Range
For Each column In theRange.Columns
If column.ColumnWidth < minColumnWidth Then
column.ColumnWidth = minColumnWidth
End If
Next column
End Sub
The above could be called as follows, to autofit the columns, but with a minimum width of 8.5:
Dim myRange As Excel.Range
Set myRange = Application.Range("A1:C3")
Call AutoFitColumns(myRange, 8.5)
You can also autofit the Rows of the Range. This is needed less often, but to do so you'd use something like:
myRange.Rows.AutoFit
Hope this helps!
Update: Reply to Craig's Comment:
Thansk Mike. This spreadsheet is used as sturctured input to a web interface generator so the column widths vary and shouldn't be adjusted. I'm really just looking for something to scan a sheet for any cells where the text displayed is wider than what the cell will allow to fit, thereby needing the row height sized larger. I'm not looking for a process to make the adjustment, just find them since they are easy to overlook. Any ideas on that?
Well, clearly you want to do two things: (1) identify which cells are being cut off, and then (2) correct these cutoffs.
I do understand your desire to do this in two distinct stages, but step (1) is almost impossible to do correctly across all circumstances with Excel because not only can row heights and column widths vary, but text wrapping can be on or off, and the font can be a non-proportional font in any number of potential styles and/or sizes.
In short, there will be no way to reliably identify which cells are being cut off, not without an incredible amount of work. It could be mitigated, however, if you restrict the spreadsheet to only one font style and use a non-proportional font. If you do this, then you could simply compare the column width to the length of the text within the cell. This is because the Excel.Range.ColumnWidth property returns its width calibrated so that one unit of column width is equal to the width of one character in the Normal style. For proportional fonts, the width of the character 0 (zero) is used.
Therefore, for simple cases, where word wrap is not employed and the font is in the Normal style, and, ideally, the Normal font is a non-proportional font, then you could loop through all cells in the range looking for where the value held is longer than the column width:
Dim myRange As Range
Set myRange = Application.Range("A1:E5")
Dim cell As Excel.Range
For Each cell in myRange.Cells
If Len(CStr(cell.Value)) > cell.ColumnWidth Then
MsgBox "The cell at " & cell.Address & " has text that is too long!"
End if
Next cell
But now here comes the next part... How will you correct this? If, again, you have a very simple situation where word wrap is not employed and the font is in the Normal style, and, ideally, the Normal font is a non-proportional font, then you have a few options:
(1) Set the column width to match the cell's value length:
Dim myRange As Range
Set myRange = Application.Range("A1:E5")
Dim cell As Excel.Range
For Each cell in myRange.Cells
If Len(CStr(cell.Value)) > cell.ColumnWidth Then
cell.ColumnWidth = Len(CStr(cell.Value))
End if
Next cell
(2) Auto-fit the Column:
Dim myRange As Range
Set myRange = Application.Range("A1:E5")
Dim cell As Excel.Range
For Each cell in myRange.Cells
If Len(CStr(cell.Value)) > cell.ColumnWidth Then
cell.Columns.AutoFit
End if
Next cell
But if we are going to auto-fit the column, then it is much easier to just call it directly, without checking the column widths first, because not only will the widths be corrected for all cells at one time, but the Range.AutoFit method can handle any font, including non-proportional fonts, in any style.
Therefore, going directly to the auto-fit approach, we have two options: either autofit the columns, or autofit the rows. Based on your most recent quote, it sounds like you want to re-size the rows:
I'm really just looking for something to scan a sheet for any cells where the text displayed is wider than what the cell will allow to fit, thereby needing the row height sized larger.
For this I would employ text-wrapping and then autofit the rows. For example,
Dim rng As Excel.Range
Set rng = Application.Range("A1:E5")
With rng.Rows
.WrapText = True
.AutoFit
End With
I think that these are about all your options. In the end, what you want to do and what Excel is capable of doing might not match exactly, but I think you should be able to get done what you need to? Let us know if it does the trick...
-- Mike
这篇关于查找显示内容太小的文本单元格的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
