Figuring out MAX LEN in a given column

[Maki]

Hi.

I'm trying to redesign a database, originally done in Access 2003, so
exported tables to Excel to analyse existing records, and in this instance,
maximum LEN in each column that's text.
I want to program a macro which:
1. asks, via InputBox, which column to analyse;
2. starting from the row after the header, counts LEN for the record and
moves on to the next row; and
3. returns, via MsgBox, MAX value amongst all LEN counted.

I've been trying to teach myself Excel VBA programming by following John
Walkenbach's "Excel 2007 Power Programming with VBA" but I'm obviously not
"getting it" and can't even program to do COUNTA, starting with InputBox and
ending with the value shown with MsgBox.

Please help!

 

[Jacob Skaria]

Try the below macro..

Sub MyMacro()
Dim lngRow As Long, varCol As Variant, intMaxLen As Integer

varCol = InputBox("Enter the column to analyse")
If IsNumeric(varCol) Then varCol = CLng(varCol)

For lngRow = 2 To Cells(Rows.Count, varCol).End(xlUp).Row
If Len(Cells(lngRow, varCol)) > intMaxLen Then
intMaxLen = Len(Cells(lngRow, varCol))
End If
Next

MsgBox intMaxLen
End Sub

If you are interested in a formula try the below. Please note that this is
an array formula. You create array formulas in the same way that you create
other formulas, except you press CTRL+SHIFT+ENTER to enter the formula. If
successful in 'Formula Bar' you can notice the curly braces at both ends like
"{=<formula>}"

=MAX(LEN(D21000))

 

[Maki]

Thank you so much, Jacob. It worked treat!

I thought I needed to use Application.WorksheetFunction.Max() but didn't
know quite how to make it work.
Yours is heaps simpler and effective!

Thanks again.