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

 

[JB2020]

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]

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]

Give this a try with a copy of your workbook.

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

 

[nuckfuts]

Give this a try with a copy of your workbook.

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]

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.

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]

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.

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]

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:

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]

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:

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]

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?