CODEWITHSUNDEEP
excelvba
HotSauceCoconuts
HotSauceCoconuts

Reputation: 321

Is the a quicker way to determine the largest string length in an array?

This is a segment of code that has been troubling me, as I feel certain some simple function exists that will make looping through the array values redundant.

Instead I have used an array, a loop and a boolean to tell me whether the cells are empty (or test their length) and an If statement to run the last part of the code.

I thought perhaps Max would work but I believe that is only for integers. (See the debug.print part

Dim arrArchLoc As Variant
Dim boolArchLoc As Boolean
Dim rowCounter As Long

boolArchLocEmpty = False

arrArchLoc = ActiveSheet.Range(Cells(2, colArchiveLocation), Cells(lastRow, colArchiveLocation))
For rowCounter = LBound(arrArchLoc) To UBound(arrArchLoc)
    If Cells(rowCounter, colArchiveLocation) <> "" Then boolArchLocEmpty = True
Next rowCounter

'Debug.Print workshetfunction.Max(arrArchLoc)

If boolArchLocEmpty = True Then
    ActiveSheet.Cells(1, colArchiveLocation).Value = "Arch Loc"
    Columns(colArchiveLocation).ColumnWidth = 6
End If

Does such a function or simple method exist?

EDIT: Whilst that specialcells(xlCellTypeBlanks) solution looks pretty good, I would still rather get the string length solution.

My apologies, the code initially had something like...

If len(Cells(rowCounter, colArchiveLocation)) > 6 then...

but I have since removed it after having to get something in place that would work.

Is there something I could do with LEN(MAX)? I experimented with it but didn't get very far.

Upvotes: 1

Views: 505

Answers (3)

GSerg
GSerg

Reputation: 78190

Given the range is A2:A100, the result you want would be expressed on the sheet as an array formula:

={MAX(LEN(A2:A100))}

In order to execute that from VBA as an array formula and not a regular formula, you need to use Evaluate:

max_len = Evaluate("=MAX(LEN(A2:A100))")

Or, in terms of your code,

Dim arrArchLoc As Range

With ActiveSheet
  Set arrArchLoc = .Range(.Cells(2, colArchiveLocation), .Cells(lastRow, colArchiveLocation))
End With

Dim max_len As Long
max_len = Application.Evaluate("=MAX(LEN(" & arrArchLoc.Address(external:=True) & "))")

However it is much better to calculate it explicitly with a loop, like you were already doing.

Upvotes: 2

Siddharth Rout
Siddharth Rout

Reputation: 149325

Another way to check if the range is empty or not

Sub Sample()
    Debug.Print DoesRangeHaveEmptyCell(Range("A1:A10")) '<~~ Change as applicable
End Sub

Function DoesRangeHaveEmptyCell(rng As Range) As Boolean
    If rng.Cells.Count = Application.WorksheetFunction.CountA(rng) Then _
    DoesRangeHaveEmptyCell = False Else DoesRangeHaveEmptyCell = True
End Function

Upvotes: 1

Nathan_Sav
Nathan_Sav

Reputation: 8531

Why not something like so

activesheet.range(cells(1,1),cells(10,1)).specialcells(xlCellTypeBlanks)

Upvotes: 1

Related Questions