Solved

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

Posted on 2014-03-01
12
1,187 Views
Last Modified: 2014-03-06
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
Comment
Question by:Dale Fye (Access MVP)
  • 3
  • 3
  • 2
  • +2
12 Comments
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 39897180
Try this

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

Author Comment

by:Dale Fye (Access MVP)
ID: 39897192
Any way to do that without pushing the value to a cell first, just going straight to a variable:

intMaxColLen = ????
0
 
LVL 45

Expert Comment

by:aikimark
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

Open in new window

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 80

Expert Comment

by:byundt
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

Open in new window

0
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
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 45

Expert Comment

by:aikimark
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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 85

Accepted Solution

by:
Rory Archibald earned 500 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

Open in new window


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 80

Expert Comment

by:byundt
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.

Brad
0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 39900705
Brad,

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 80

Expert Comment

by:byundt
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.

Brad
0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 39900821
No worries. :)
0
 
LVL 47

Author Closing Comment

by:Dale Fye (Access MVP)
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

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Excel - Row Height +1 VBA 2 24
TT Status Chang 3 31
Cells protection in Excel 7 18
Added a column screws up code 5 0
This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
Viewers will learn the basics of slicers and timelines for both PivotTables and standard Excel tables in Excel 2013.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

757 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now