Find text cells that are too small to display content

Question

I have a worksheet with cells that can contain text that sometimes exceeds the width and height of the cell. If I don’t notice this and adjust the height of the line, the text appears as a cutoff. Does anyone have a VBA snippet or tool that can find these cells so that I can customize them? Thank!

Answer 1

Determining which column is too wide will be difficult, especially because the text inside the cell can have different fonts and / or font size. It is much easier to automatically sort the columns using the Range.AutoFit method .

AutoFit :

Dim myRange As Excel.Range
Set myRange = Application.Range("A1:C3")
myRange.Columns.AutoFit

Range.AutoFit , . , :

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

, , 8,5:

Dim myRange As Excel.Range
Set myRange = Application.Range("A1:C3")
Call AutoFitColumns(myRange, 8.5)

. , - :

myRange.Rows.AutoFit

, !

: Craig:

. - . - , , , . , , . ?

, , : (1) , , (2) .

, (1) Excel, , / .

, , , . , . , . , Excel.Range.ColumnWidth , . 0 ().

, , , , , , , :

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

... ? , , , , , , , , :

(1) :

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) :

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

, , , , Range.AutoFit , , .

, , : , . , , :

- , , , .

, . ,

Dim rng As Excel.Range
Set rng = Application.Range("A1:E5")

With rng.Rows
    .WrapText = True
    .AutoFit
End With

, . , , Excel , , , , ? , ...

-