CODEWITHSUNDEEP
arraysexcelvbaloops
pgSystemTester
pgSystemTester

Reputation: 9932

How To Modify A Range of Cells Without a Loop

I have a massive Excel template that's having performance issues. I'd like to optimize the VBA code I'm using to modify cells to be more of an "all at once" approach. I have several basic functions using loops to modify values. Here's an example:

Dim aCell as Range
For Each aCell In Range("A1:A9999").Cells

    'appends prefix to value of each cell
     aCell.Value = "CC_" & aCell.Value

Next aCell

While this works, the drawback of this is that it causes several recalculations and updates that slows down the template. I'm familiar with turning calculations/screen updating on and off, but for reasons I won't go into, that's not an option.

This code below does NOT work, but it's the approach I'm looking for. Is there a way to make such a change using an array or some other tool I'm not thinking of that would minimize the templates calculation updates?

Range("A1:A9999").Value = "CC_" & Range("A1:A9999").Value

Thank you!

Upvotes: 0

Views: 1282

Answers (4)

pgSystemTester
pgSystemTester

Reputation: 9932

I was revisiting this (trying to still make it faster) and now that I have a little better understanding, I would recommend an approach shown below. The accepted answer is hard-coded for one column, which was my example asked, but the below approach is more dynamic.

Sub sampleArraySheetEdit()
Dim aRange As Range: Set aRange = Range("A1:B9999") ' or whatever your range is...
Dim vRng() As Variant: vRng = aRange
Dim r As Long, c As Long


For r = LBound(vRng, 1) To UBound(vRng, 1) 'this ensures all cells always accounted for
    For c = LBound(vRng, 2) To UBound(vRng, 2)

        'perform you operation here....
        vRng(r, c) = "CC_" & vRng(r, c)

    Next c
Next r

aRange = vRng

End Sub

Upvotes: 0

Ron Rosenfeld
Ron Rosenfeld

Reputation: 60464

Reading/writing to/from the worksheet takes a lot of time. Do the modifications within a VBA array, then write it back.

Dim myRange As Range, myArr As Variant
Set myRange = Range("A1:A9999")

myArr = myRange

For i = 1 To UBound(myArr, 1)
    myArr(i, 1) = "CC_" & myArr(i, 1)
Next i

myRange = myArr

Upvotes: 2

Rory
Rory

Reputation: 34075

FWIW, you can do it without a loop using Evaluate like this:

Sub addText()
     With Range("A1:A9999")
        .Value2 = .Worksheet.Evaluate("INDEX(""CC_""&" & .Address & ",)")
    End With
End Sub

Upvotes: 1

Greg Viers
Greg Viers

Reputation: 3523

You could temporarily create a column full of functions, then paste those values over the column A values:

Range("XFD1:xfd9999").Formula = "=""CC_""&A1"
Calculate
Range("A1:a9999").Value = Range("XFD1:XFD8").Value
Range("XFD1:XFD9999").ClearContents

I'm operating on the assumption here that you are not using column XFD for anything else. If you are, you could use a different column for this purpose.

Upvotes: 1

Related Questions