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
 

Some videos you may like

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.

JB2020

Board Regular
Joined
Jul 29, 2020
Messages
75
Office Version
  1. 365
  2. 2016
  3. 2010
Platform
  1. Windows
Using Excels copy and paste is generally quicker than using arrays, so I would avoid bringing arrays in and out of VBA if possible. I would personally recommend either using advanced filter, applying an autofilter then copying across or using power query. It should speed it up and make it more simple too.
 

nuckfuts

New Member
Joined
Mar 10, 2020
Messages
43
Office Version
  1. 365
Platform
  1. Windows
Using Excels copy and paste is generally quicker than using arrays, so I would avoid bringing arrays in and out of VBA if possible. I would personally recommend either using advanced filter, applying an autofilter then copying across or using power query. It should speed it up and make it more simple too.
Is there a way you would suggest? All the sites mention arrays are quicker as it saves the data into an array and then you can paste as one instead of going into the sheet for each paste. I have a little experience with power query but is there a way to pull filtered data into a column (starting at C6) ?
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
47,926
Office Version
  1. 365
Platform
  1. Windows
Give this a try with a copy of your workbook.

VBA Code:
Sub CopyPasteArrays_v2()
  Application.ScreenUpdating = False
  With Sheets("SRCE")
    With .Range("A7", .Range("A" & Rows.Count).End(xlUp))
      .AutoFilter Field:=1, Criteria1:="condition 1", Operator:=xlOr, Criteria2:="condition 2"
      .Offset(1).Resize(.Rows.Count - 1).Copy Destination:=Sheets("DEST").Range("C6")
    End With
    .AutoFilterMode = False
  End With
  Application.ScreenUpdating = True
End Sub
 
Solution

nuckfuts

New Member
Joined
Mar 10, 2020
Messages
43
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Give this a try with a copy of your workbook.

VBA Code:
Sub CopyPasteArrays_v2()
  Application.ScreenUpdating = False
  With Sheets("SRCE")
    With .Range("A7", .Range("A" & Rows.Count).End(xlUp))
      .AutoFilter Field:=1, Criteria1:="condition 1", Operator:=xlOr, Criteria2:="condition 2"
      .Offset(1).Resize(.Rows.Count - 1).Copy Destination:=Sheets("DEST").Range("C6")
    End With
    .AutoFilterMode = False
  End With
  Application.ScreenUpdating = True
End Sub
This worked great! Still a small wait but decreased loading time dramatically - thanks!
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
47,926
Office Version
  1. 365
Platform
  1. Windows
Still a small wait
You are right about arrays often being a quicker way to go but it does depend on the individual circumstances. Give this a try and see how it compares with the same data.

VBA Code:
Sub CopyPasteArrays_v3()
  Dim a As Variant, b As Variant
  Dim i As Long, k As Long
 
  With Sheets("SRCE")
    a = .Range("A8", .Range("A" & Rows.Count).End(xlUp)).Value
  End With
  ReDim b(1 To UBound(a), 1 To 1)
  For i = 1 To UBound(a)
    If a(i, 1) = "condition 1" Or a(i, 1) = "condition 2" Then
      k = k + 1
      b(k, 1) = a(i, 1)
    End If
  Next i
  If k > 0 Then Sheets("DEST").Range("C6").Resize(k).Value = b
End Sub
 

nuckfuts

New Member
Joined
Mar 10, 2020
Messages
43
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

You are right about arrays often being a quicker way to go but it does depend on the individual circumstances. Give this a try and see how it compares with the same data.

VBA Code:
Sub CopyPasteArrays_v3()
  Dim a As Variant, b As Variant
  Dim i As Long, k As Long

  With Sheets("SRCE")
    a = .Range("A8", .Range("A" & Rows.Count).End(xlUp)).Value
  End With
  ReDim b(1 To UBound(a), 1 To 1)
  For i = 1 To UBound(a)
    If a(i, 1) = "condition 1" Or a(i, 1) = "condition 2" Then
      k = k + 1
      b(k, 1) = a(i, 1)
    End If
  Next i
  If k > 0 Then Sheets("DEST").Range("C6").Resize(k).Value = b
End Sub
This code also works - but it just shortened the run time from 5.54s to 5.49s so I'll probably just stick with the original - thanks!
 

JB2020

Board Regular
Joined
Jul 29, 2020
Messages
75
Office Version
  1. 365
  2. 2016
  3. 2010
Platform
  1. Windows
Is there a way you would suggest? All the sites mention arrays are quicker as it saves the data into an array and then you can paste as one instead of going into the sheet for each paste. I have a little experience with power query but is there a way to pull filtered data into a column (starting at C6) ?

The solution Peter provided is good and sounds like it works well for what you need. If you are interested in how to use the advanced filter option, which may offer a speed advantage if you are still looking for ways to optimize, then I would go for something like this:

VBA Code:
Sub CopyPasteAF()
  
    Dim SourceDatarange As Range, CriteriaRange As Range, CopyToRange As Range
  
    Application.ScreenUpdating = False
  
        Set SourceDatarange = Sheets("SRCE").Range("A7", Sheets("SRCE").Range("A" & Rows.Count).End(xlUp))
        Set CriteriaRange = Sheets("SRCE").Range("B1:B3")
        Set CopyToRange = Sheets("DEST").Range("C5")
        
        CriteriaRange = Application.Transpose(Array(Sheets("SRCE").Range("A6").Value, "condition 1", "condition 2"))
    
        SourceDatarange.AdvancedFilter xlFilterCopy, CriteriaRange, CopyToRange
        
        CriteriaRange.ClearContents
      
    Application.ScreenUpdating = True

End Sub

There area a couple of caveats for this option. Your conditions need to be in cells somewhere, I have just put B1:B3 here, but you will probably want to change that to an unused range so not to overwrite anything. This just consists of the heading name from your SRCE range, and your conditions in the cells below it. I have added these in during the code, but you can just delete those lines out and store them in the cells if you want. Also the 'CopyToRange' (the paste location) needs to be a cell containing a matching heading from the SRCE sheet in this example, though you can change it and then change it then change it back during the code if required.
 

nuckfuts

New Member
Joined
Mar 10, 2020
Messages
43
Office Version
  1. 365
Platform
  1. Windows
The solution Peter provided is good and sounds like it works well for what you need. If you are interested in how to use the advanced filter option, which may offer a speed advantage if you are still looking for ways to optimize, then I would go for something like this:

VBA Code:
Sub CopyPasteAF()
 
    Dim SourceDatarange As Range, CriteriaRange As Range, CopyToRange As Range
 
    Application.ScreenUpdating = False
 
        Set SourceDatarange = Sheets("SRCE").Range("A7", Sheets("SRCE").Range("A" & Rows.Count).End(xlUp))
        Set CriteriaRange = Sheets("SRCE").Range("B1:B3")
        Set CopyToRange = Sheets("DEST").Range("C5")
       
        CriteriaRange = Application.Transpose(Array(Sheets("SRCE").Range("A6").Value, "condition 1", "condition 2"))
   
        SourceDatarange.AdvancedFilter xlFilterCopy, CriteriaRange, CopyToRange
       
        CriteriaRange.ClearContents
     
    Application.ScreenUpdating = True

End Sub

There area a couple of caveats for this option. Your conditions need to be in cells somewhere, I have just put B1:B3 here, but you will probably want to change that to an unused range so not to overwrite anything. This just consists of the heading name from your SRCE range, and your conditions in the cells below it. I have added these in during the code, but you can just delete those lines out and store them in the cells if you want. Also the 'CopyToRange' (the paste location) needs to be a cell containing a matching heading from the SRCE sheet in this example, though you can change it and then change it then change it back during the code if required.
Thanks for the follow up! I'll let you know if I give this a try today and the resulting run time :)
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
47,926
Office Version
  1. 365
Platform
  1. Windows
This code also works - but it just shortened the run time from 5.54s to 5.49s
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?
 

Watch MrExcel Video

Forum statistics

Threads
1,123,265
Messages
5,600,605
Members
414,394
Latest member
mahendar

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