Speed up for each loop with variant array. Excel VBA

Question

I've got a worksheet that pulls a lot of data together from the rest of the workbook. My code then runs a 'for each' loop to remove any spaces in the data. The problem is i've got 60 instances of this code to run. From named ranges A1 to A60. My question is can i speed this section of the code up by using arrays? If i'm given pointers with this instance i can apply it to the other instances. I've no experience with arrays hence my need for help.

Note that it's best to post your code as a code-block instead of a screenshot. — JvdV
– JvdV, Commented Jun 8, 2021 at 7:10

JvdV · Accepted Answer · 2021-06-08 07:31:56Z

2

I'd look into:

A1.Value = Application.Substitute(A1, " ", "")

Or:

A1.Replace what:=" ", Replacement:="", Lookat:=xlPart

Note that you can add more of the parameters of Range.Replace() to avoid any possible mishaps since this function will save and use settings of previous attempts using this function.

edited Jun 8, 2021 at 7:31

answered Jun 8, 2021 at 7:15

JvdV

76.8k8 gold badges48 silver badges89 bronze badges

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

Comments

Tim Williams · Accepted Answer · 2021-06-08 07:11:51Z

1

Like this:

Dim arr, r as long, v
'...
arr = A1.Value                 'get range values as array
for r = 1 to ubound(arr, 1)
    v = arr(r, 1)
    if len(v) > 0 Then arr(r, 1) = replace(v, " ", "")
next r
A1.Value = arr                 'return values to range

answered Jun 8, 2021 at 7:11

Tim Williams

169k8 gold badges104 silver badges143 bronze badges

Collectives™ on Stack Overflow

Speed up for each loop with variant array. Excel VBA

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related