Changing a For/Next Loop into a Variant Array

Question

I am tring to make my code run faster. I believe the proper technique would be to use a Variant Array. Can you provide me a sample on how to translate the following code into a more efficient Variant Array? Thanks!

Dim i As Long
LastRow = Range("A" & Rows.Count).End(xlUp).Row

For i = 2 To LastRow


    If Range("S" & i) > 0.0014 Then
        Range("Z" & i, "AA" & i).Copy
        Range("AC" & i, "AD" & i).PasteSpecial xlPasteValues
    End If



Application.ScreenUpdating = False
Next i

Doug Glancy · Accepted Answer · 2012-12-21 20:53:40Z

2

Here's an alternative that just pastes formulas into the specified range, and then copies over them as values:

Sub FastPaste()
Dim LastRow As Long

LastRow = Range("A" & Rows.Count).End(xlUp).Row
With Range("AC2" & ":AD" & LastRow)
    .FormulaR1C1 = "=IF(RC19>0.0014,RC[-3],"""")"
    .Copy
    .PasteSpecial (xlPasteValues)
End With
End Sub

answered Dec 21, 2012 at 20:53

Doug Glancy

27.5k6 gold badges72 silver badges118 bronze badges

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

3 Comments

Daniel Over a year ago

That'd be preferable if it'll fit the OPs requirements... But that method will result in blank cells in columns AC & AD for any row where S < 0.0014. The only way I can think of to correct that would still require looping.

Chris2015 Over a year ago

Ultimately I am just using a Countifs Statement from AC & AD so for my purposes it doesn't matter if there are blank cells where S <.0014. I guess I could just include the <.0014 in my countifs itself, but I prefer to isolate the information in columns off to the side.

Doug Glancy Over a year ago

Unless there's some reason you need to do this in code, why not also just put the ">0.0014" in the sheet itself? I obviously don't know much about what you're doing, but my instinct is that you can just handle it with formulas in the sheet.

Daniel · Accepted Answer · 2012-12-21 21:25:14Z

2

Here's an example:

Dim i As Long
Dim checkedValues 'Declare a variant
Application.ScreenUpdating = False
lastrow = Range("S" & Rows.Count).End(xlUp).Row
'Get range you're checking into the array
checkedValues = Range("S2:S" & lastrow)
For i = 1 To UBound(checkedValues)
    If checkedValues(i, 1) > 0.0014 Then
        'Transfer values directly instead of copy paste
        Range("AC" & i + 1, "AD" & i + 1).value = Range("Z" & i + 1, "AA" & i + 1).value
    End If
Next i
Application.ScreenUpdating = True

If you wanted to do more array work, you'd need to make an array of what you're actually updating and what you're updating from, and then transfer the values of the updated array back into the cells. If speed is that much of a concern, you may want to investigate to see if that would improve it.

edited Dec 21, 2012 at 21:25

answered Dec 21, 2012 at 20:49

Daniel

13.1k3 gold badges39 silver badges64 bronze badges

3 Comments

Chris2015 Over a year ago

I tried this code and with the number of rows I have it doesn't seem to speed things up. What's interesting is the more parameters I include in my IF Statement the less time it takes to run.

Chris2015 Over a year ago

I know there is a way to create transfer to a .Formula and then add back into my Sheet, but I am not real clear on how to go about this.

Daniel Over a year ago

Right, you wouldn't notice much difference in speed unless you had a LOT of data. This should be rather fast. I just changed this to checking the S column as it makes more sense to check that since you aren't using column A in your code at all.

Collectives™ on Stack Overflow

Changing a For/Next Loop into a Variant Array

2 Answers 2

3 Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related