Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
Solved

# Determine max number of characters in any cell in a column in excel, using VBA

Posted on 2014-03-01
Medium Priority
1,707 Views
Using VBA, I need to determine the maximum number of characters in a column of anywhere from 20,000 to 200,000 records, using VBA.

I found a reference that indicates I can do this with an Array function in a cell by doing something like:  =MAX(Len(A1:A20000))) and then hitting Ctrl-Shift-Enter  which wraps the function in { }, but I need to do this in code, and need to do it for multiple columns, so it would be great if the solution involved a column# rather than an alpha representation of the column.
0
Question by:Dale Fye
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
• 3
• 3
• 2
• +2

LVL 43

Expert Comment

ID: 39897180
Try this

col = 3
rw = 2000
Range("A1").FormulaArray = "=max(len(r1c" & col & ":R" & rw & "C" & col & "))"
0

LVL 48

Author Comment

ID: 39897192
Any way to do that without pushing the value to a cell first, just going straight to a variable:

intMaxColLen = ????
0

LVL 46

Expert Comment

ID: 39897969
@ssaqibh

Unfortunately, you can't use worksheetfunctions, since the Len() function is not a worksheet function.

You can create a user defined function for this.
``````Option Explicit

Public Function MaxLen(parmRange As Range) As Long
Dim strItem As String
Dim lngMaxLen As Long
Dim lngLen As Long
Dim rngCell As Range
lngMaxLen = Len(parmRange.Cells(1, 1).Value)
For Each rngCell In parmRange
lngLen = Len(rngCell.Value)
If lngMaxLen < lngLen Then
lngMaxLen = lngLen
Next
Next
MaxLen = lngMaxLen
End Function
``````
You can get even more performance if you copy the range into a variant array and iterate the array.  This is the inverse of the technique I showed in my Fast Data Push to Excel article.
http://www.experts-exchange.com/A_2253.html
0

LVL 81

Expert Comment

ID: 39898077
LEN is a perfectly valid worksheet function, so saqibh's approach will definitely work.

Rather than troll through an entire column of cells, it is more efficient to restrict it to the used range. I modified aikimark's function to do just that, adding in his suggestion of copying the range into a variant.
``````Public Function MaxLen(parmRange As Range) As Long
Dim lngLen As Long
Dim v As Variant, vData As Variant
vData = Intersect(parmRange.Worksheet.UsedRange, parmRange).Value
For Each v In vData
If Len(v) > lngLen Then lngLen = Len(v)
Next
MaxLen = lngLen
End Function
``````
0

LVL 43

Expert Comment

ID: 39898181
Not very elegant but to avoid the loop I would have tried something like

col = 3
rw = 20000
tmp = Cells(1, col + 1).FormulaR1C1
Cells(1, col + 1).FormulaArray = "=max(len(r1c" & col & ":R" & rw & "C" & col & "))"
intMaxColLen = Cells(1, col + 1).Value
Cells(1, col + 1).FormulaR1C1 = tmp
0

LVL 46

Expert Comment

ID: 39898671
when I wrote that you can't use worksheet functions, I meant that in the context of a VBA routine.  There is certainly a Len() function that can be used in a formula.
0

LVL 85

Accepted Solution

Rory Archibald earned 2000 total points
ID: 39899891
You could use Evaluate:

``````Sub GetLength(lColumn As Long)
MsgBox Evaluate("MAX(LEN(" & Application.ConvertFormula("R1C" & lColumn & ":R20000C" & lColumn, xlR1C1, xlA1) & "))")
End Sub
``````

Of course, you could pass the row number as well rather than using a fixed number, or pass a range using Cells if you only have a column Number.
0

LVL 81

Expert Comment

ID: 39900662
Rory,
I get a type mismatch with your code in Excel 2013 whenever there are more than 255 cells being tested. Your sub works when there are 255 or fewer cells being tested.

0

LVL 85

Expert Comment

ID: 39900705

I can't reproduce that. Just ran a quick test with 800 odd cells populated in a column with no problems. Can you provide a workbook?

Rory
0

LVL 81

Expert Comment

ID: 39900807
Rory,
The type mismatch occurred in Excel 2013 when I ran your macro from the Immediate pane command line. But when I ran it from a command button on the worksheet in response to your Comment requesting a workbook, your code worked. Hmm...

So I rebooted my computer (both Mac and Parallels), and now your code works both on the Immediate pane command line and when called from a worksheet command button. It also works when I turn your one-liner into a UDF called from a worksheet formula.

My computer must have had some corruption that was cured by rebooting.

I apologize for the false accusation.

0

LVL 85

Expert Comment

ID: 39900821
No worries. :)
0

LVL 48

Author Closing Comment

ID: 39910319
This worked well, since I already had the workbook open and knew how many rows to include in the string.

Thanks, Rory.
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calculâ€¦
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
###### Suggested Courses
Course of the Month6 days, left to enroll