Copy Paste with Arrays

[nuckfuts]

Hi, I'm trying to use arrays to speed up my macro. It currently copies the data I need over to "DEST" as expected. I'd like to combine arrays and then copy and paste once to speed up the process.

Below is my code - it loops through the source book "SRCE" - starting at A8 - and pastes in the next empty cell in "DEST" starting at C6

Sub CopyPasteArrays()
Application.ScreenUpdating = False
    Sheets("SRCE").Select
    Dim i As Long
    Dim y As Integer
    Dim myArray As Variant
    
    y = 6
    
    For i = 8 To 100 'source data always starts at row 8 but varies in length
        If Range("A" & i) = "End" Then 'End signifies the end of data
            Exit Sub
        Else
            If Range("A" & i) = "condition 1" Or Range("A" & i) = "condition 2" Then
                Sheets("DEST").Range("C" & y).Value = Range("A" & i).Value
                
                y = y + 1
            Else: GoTo Skip
        End If
    End If
Skip:
    Next i
Application.ScreenUpdating = True
End Sub

 

[nuckfuts]

Was the 5.54s the time for my autofilter code in post 4 or your original post 1 code or something else?

I am surprised at the time taken as my test with 900,000 rows took 1.1s - but speed does depend on system resources, anything else happening in the workbook etc of course.

BTW, about how big (no. of rows) is the data you are actually dealing with?

5.54 for the autofilter code. I only have ~40 rows in the SRCE sheet, netting me 24 filtered results pasted on the DEST sheet. I don't have a super computer but definitely up to par to a standard work computer in an excel-heavy industry. Not sure why it's still taking so long :/

 

[Peter_SSs]

Perhaps you have a lot of formulas in the sheet that need to recalculate after the filter has been applied?
However, with the array method that would not be applicable and with so few rows that method should basically be instantaneous. Can't see how it could take several seconds to process 40 rows in an array. With my sample data of 50 rows the post #6 code took less than 0.03 seconds.

 

[nuckfuts]

Perhaps you have a lot of formulas in the sheet that need to recalculate after the filter has been applied?
However, with the array method that would not be applicable and with so few rows that method should basically be instantaneous. Can't see how it could take several seconds to process 40 rows in an array. With my sample data of 50 rows the post #6 code took less than 0.03 seconds.

Agreed - the array should've sidestepped waiting for calculations... I think I've set I've turned manual calc off and on as well but doesn't seem to do much for runtime. I'll play with the formulas today and see what I come up with. I also have conditional formatting applied to the sheet if that could be slowing things down as well?

 

[nuckfuts]

@Peter_SSs I timed just the code runtime independently - in other words, excluding sheet calculations - and it runs in 0.09s. I've already got the overall process (with cell updates) down below 3 seconds by limiting lookup arrays. Not sure what else I can optimize besides shortening lookup arrays but headed in the right direction!

 

[Peter_SSs]

I timed just the code runtime independently - in other words, excluding sheet calculations - and it runs in 0.09s.

Which code? The only sheet calculations related to the array code from post 6 would be those calculations dependant on the values in column C of 'DEST' and any such resulting calculations are not really part of the run-time of the code but are subsequent to that code.

 

[nuckfuts]

@Peter_SSs

Which code? The only sheet calculations related to the array code from post 6 would be those calculations dependant on the values in column C of 'DEST' and any such resulting calculations are not really part of the run-time of the code but are subsequent to that code.

Sorry if my verbiage was confusing. By code I meant just the code you posted. The macro process would be slowed down because I have calculations within the 'DEST' sheet that were reprocessing. So I 1) limited my lookups (I had a lot of lookups formulas to whole columns in other sheets) and 2) replacing an array formula with a normal formula work-around. The overall macro (with calculation updates in the sheet) took 0.23s this morning!

 

[Peter_SSs]

By code I meant just the code you posted.

I posted two different codes (_v2 and _v3) so I was just checking which one.
However, it seems like you have it all down to a satisfactory level now so it doesn't matter.

 

[nuckfuts]

I posted two different codes (_v2 and _v3) so I was just checking which one.
However, it seems like you have it all down to a satisfactory level now so it doesn't matter.

Ah it was _v2.. Code _v3 performed similarly to v2 but I understand v2 more so I rolled with that one - I'm sure v3 will come in handy down the road!

 

[Peter_SSs]

OK, thanks for the clarification.