Efficient looping through an array

Question

I have a column of full names that I would like to replace with initials.

I am currently doing this:

Dim n As Long
Dim varray As Variant
Dim FullNameRange As Range
Dim FullName As String

varray = Range(NDLstaffCol & FirstLineItemRow & ":" & staffCol & LastRow + 1)

For n = FirstLineItemRow To UBound(varray, 1)
    Set FullNameRange = ActiveSheet.Range(staffCol & n)
    FullName = FullNameRange.Value
    FullNameRange = Left(FullName, 1) & Mid$(FullName, InStr(FullName, " ") + 1, 1)
Next

Where LastRow refers to the last record as a global variable.

This takes a long time to process compared to other subroutines in the same module.

Is there a better way to do this?

For reference, I never know the full names so a hard-coded find/replace would not work. There are <500 records.

Looks fine to me, it has a runtime complexity of O(n) which can't be improved upon (at least in this universe). Have you tried running outside of the debugger? Does it run just as slowly in debug mode and normally? Have you tried rewriting it in C#/.NET using the .NET Office SDK? Does it run faster if you minimize Excel as you run it? What version of Excel are you using? — Dai
– Dai, Commented Oct 17, 2016 at 23:48
@Dai It runs slowly normally when 'live'. I use a progress bar (a userform) to show the user where they are in the subroutine and when I get to this section it takes so long. Not heard of rewriting and using SDK. Minimising doesn't impact time. Excel 2016. If this is really the best way to do it, then I'm glad I only have <500 records!! — redditor
– redditor, Commented Oct 17, 2016 at 23:52

Tim Williams · Accepted Answer · 2016-10-18 00:31:00Z

3

Dim n As Long, rng As Range
Dim varray As Variant
Dim FullNameRange As Range
Dim FullName As String, v

Set rng = Range(staffCol & FirstLineItemRow & ":" & staffCol & LastRow + 1)
varray = rng.Value

For n = 1 To UBound(varray, 1)
    v = varray(n, 1)
    varray(n, 1) = Left(v, 1) & Mid$(v, InStr(v, " ") + 1, 1)
Next

rng.Value = varray

answered Oct 18, 2016 at 0:31

Tim Williams

169k8 gold badges104 silver badges143 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

redditor Over a year ago

Wow. Just tested this and it's shaved off about 2 minutes from my total... Thank you! Can you explain why this worked and maybe where I was going wrong?

Tim Williams Over a year ago

You pulled the data into an array, but then looped cell-by-cell over the worksheet: it's much faster to alter the data in the array and then write that back to the sheet in one operation.

redditor Over a year ago

Haha, that is exactly why I meant to put it in an array - thank you so much!

Collectives™ on Stack Overflow

Efficient looping through an array

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related