Need to speed up code (copy/paste from one sheet to another)

Question

Thank you for the help in advance. I managed to create a macro that searches Column O of a sheet called "ClientTradeDetails" for the cell value "True" and, if a cell value is "True" , it takes that entire row and pastes it into a sheet called "TrueValues".

It works, BUT it is extremely slow. As you can see, I have 65536 rows worth of data in my master sheet to go through. I was thinking Copy/Paste was the issue but I don't know how to change this to avoid the Copy method. Any help?

Sub MoveTrue()

Sheets("ClientTradeDetails").Select

 Dim tfCol As Range, Cell As Object

    Set tfCol = Range("O2:O439050") 'Substitute with the range '

    For Each Cell In tfCol

        If Cell.Value = "True" Then
            Cell.EntireRow.Cut
            Sheets("TrueValues").Select
            ActiveSheet.Range("A65536").End(xlUp).Select
            Selection.Offset(1, 0).Select
            ActiveSheet.Paste
        End If

    Next

End Sub

The issue is that you have that much data in a single excel sheet and you expect a macro to make up for poor data management choices. — DejaVuSansMono, Feb 07 '17 at 20:36
Use the [AutoFilter method](https://msdn.microsoft.com/en-us/library/office/aa221844.aspx) to filter for true on one columns and bulk transfer the values. Repeat for other columns if need be. You are currently searching (for all intents and purposes) the entire worksheet. Surely you only have a few columns that may contain true. — , Feb 07 '17 at 20:48
What @Jeeped means to say I think is replace `Cell.Entire.Cut` with something like `Intersect(UsedRange,Cell.EntireRow).Cut`. In this way you only copy needed columns. Not the entire row (which is probably not filled across all columns. Also see [avoiding select](http://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba-macros). That will increase your speed greatly. Also, depending on the amount of values AutoFitler can be slow too, but definitely faster than looping all those rows. — Scott Holtzman, Feb 07 '17 at 20:51
@ScottHoltzman - That is a good point but I was actually referring to `For Each Cell In tfCol` when `fCol` is defined as full rows. Oh, wait... that's an 'oh', not a zero. In any event, autofilter and bulk transfer is faster than individual cell checking and copy and paste single lines. — , Feb 07 '17 at 20:53
At least cut out the selecting: you can cut in one line: `Cell.EntireRow.Cut Sheets("TrueValues").Range("A65536").End(xlUp).Offset(1, 0)` — Tim Williams, Feb 07 '17 at 20:55
If you "have 65536 rows worth of data in my master sheet to go through", why does your range go to row 439050? And, if you have more than 65536 possible rows to copy, your `Range("A65536").End(xlUp)` won't work - it probably needs to be `Range("A1048576").End(xlUp)` (or, preferably, use an appropriate `Rows.Count` instead of a hard-coded value). — YowE3K, Feb 07 '17 at 21:01

score 0 · Answer 1 · answered Feb 08 '17 at 18:52

This may be a bit more complex than you were looking for, but it works far more efficiently than copying and pasting data:

Sub GetTrueValues()

Dim ws As Worksheet
Dim arr() As Variant
Dim arrFound() As Variant
Dim arrOut() As Variant

Dim i As Long
Dim j As Long
Dim k As Long

Dim lConst As Long: lConst = 15 ' For the O column

Set ws = ActiveWorkbook.Sheets("SheetName")
arr() = ws.UsedRange.Value

For i = LBound(arr()) To UBound(arr())
    If arr(i, lConst) = "True" Then
        k = k + 1
        ReDim arrFound(1 To k)
        arrFound(k) = i
    End If
Next

ReDim arrOut(1 To k, 1 To UBound(arr(), 2))
For i = 1 To UBound(arrFound())
    For j = LBound(arr()) To UBound(arr(), 2)
        arrOut(i, j) = arr(arrFound(k), j) ' Using the previously stored integer,
                                           ' retrieve the records of interest.
    Next
Next

ActiveWorkbook.Sheets.Add
ActiveSheet.Range("A1").Resize(UBound(arrOut(), 1), UBound(arrOut(), 2)).Value = arrOut()

End Sub

What this macro essentially does is finds the number of records that have the value of true, it then puts all of these into an array and spits the array back out into a worksheet. You can change the part where it prints it back out as needed.

Need to speed up code (copy/paste from one sheet to another)

1 Answers1