Copy Paste with Arrays

nuckfuts

New Member
Joined
Mar 10, 2020
Messages
43
Office Version
  1. 365
Platform
  1. Windows
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

VBA Code:
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

New Member
Joined
Mar 10, 2020
Messages
43
Office Version
  1. 365
Platform
  1. Windows
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 :/
 

Some videos you may like

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
47,926
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Mar 10, 2020
Messages
43
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Mar 10, 2020
Messages
43
Office Version
  1. 365
Platform
  1. Windows
@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

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
47,926
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

New Member
Joined
Mar 10, 2020
Messages
43
Office Version
  1. 365
Platform
  1. Windows
@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

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
47,926
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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. (y)
 

nuckfuts

New Member
Joined
Mar 10, 2020
Messages
43
Office Version
  1. 365
Platform
  1. Windows
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. (y)
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

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
47,926
Office Version
  1. 365
Platform
  1. Windows
OK, thanks for the clarification.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,263
Messages
5,595,166
Members
413,973
Latest member
leon1974mk

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top