Vba to tell me what cell in range has the most letters

[tonywatsonhelp]

Hi Everyone,
I'm trying to makea grid area where every cell is the same size,

I have 500 cells and keep finding when I resize some text in some cells is missing

is there a macro I could run that would tell me which cell has the longest amount of text?

Thanks

Tony

 

[mumps]

Try:

Sub FindLength()
    Application.ScreenUpdating = False
    Dim rng As Range, cnt As Long, sAddr As String
    For Each rng In Range("[COLOR="#FF0000"]A1:J50[/COLOR]")
        If Len(rng) > cnt Then
            cnt = Len(rng)
            sAddr = rng.Address(0, 0)
        End If
    Next rng
    MsgBox ("The longest text is in cell " & sAddr)
    Application.ScreenUpdating = True
End Sub

Change the range (in red) to suit your needs.

 

[tonywatsonhelp]

Thanks mumps, that's great
Tony

 

[mumps]

My pleasure.