How can I make my code faster? (Copying columns)

Question

I'm new to vba and wrote the code below to match and copy columns; I'm trying to figure out how to make it more efficient as it takes some time to churn out the output. Any advice or feedback would be much appreciated! I thought of switching screen updating to false but it doesn't really speed things up. Could it be slow because I am using the autofilter method of filtering cells? Thank you in advance.

Sub GetItems()
    Dim cel As Range
    Dim celAddress As Range
    Dim n As Integer
    Dim SelRange As Range
    Dim BrandsPasteLoc As Range

    n = 1

    'Application.ScreenUpdating = False

    Worksheets("CA").Activate
    ActiveSheet.Range("$A$1:$L$2622").AutoFilter Field:=10, Criteria1:="<>#N/A", 
    Criteria2:="<>0", Operator:=xlFilterValues

    Worksheets("Items to push to CA").Activate

    For Each cel In Range("A1:O1")
        Brand = cel.Value
        If Brand = "" Then
            Resume Next
        End If
        Worksheets("CA").Activate
        Application.Workbooks("Items Suggestion_CA").Worksheets("MY").Range("$A$1:$M$3959").AutoFilter Field:=5, Criteria1:=Brand, Operator:=xlFilterValues


        Range("B:B").Sort _
           Key1:=Range("b1"), Order1:=xlDescending     'sorts on Quantity

        Columns("A:A").Select
        Selection.SpecialCells(xlCellTypeVisible).Select
        Selection.Copy
        Sheets("Items to push to CA").Select
        Set SelRange = ActiveSheet.Columns(n)
        Set BrandsPasteLoc = Range(Range("A1:Z1").Find(Brand).Offset(1), Range("A1:Z1").Find(Brand).Offset(1).End(xlDown))
        BrandsPasteLoc.Select
        ActiveSheet.Paste
        Worksheets(“CA”).Activate
        n = n + 1
    Next cel

    Worksheets("Items to push to CA").Rows(2).Delete

    Worksheets("Items to push to CA").Activate
    Columns("A:O").Select
    Selection.EntireColumn.AutoFit
    Worksheets("Items to push to CA").Rows(60 & ":" & 
    Sheet1.Rows.Count).ClearContents

    Worksheets("CA").Activate
    Cells.AutoFilter

    Worksheets("Items to push to CA").Activate

    'Application.ScreenUpdating = True
End Sub

First of all avoid using `.Activate`, `.Select` and `.Selection`: [How to avoid using Select in Excel VBA](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) — Pᴇʜ, Nov 20 '18 at 09:47
This would better fit to [Stack Exchange: Code Review](https://codereview.stackexchange.com) — Pᴇʜ, Nov 20 '18 at 09:48

score 0 · Answer 1 · answered Mar 11 '20 at 09:18

0

.Activate, .Select and AutoFilter are very slow. Try to change your Range into array and do the operations, and with some smarter search algorithm before copying to the target location. This should boost up the speed by a few times at least.

answered Mar 11 '20 at 09:18

Oliver Leung

740
5
12

score -1 · Answer 2 · answered Nov 20 '18 at 09:47

-1

This question is more for Code Review but try adding Application.ScreenUpdating=False when the sub starts and Application.ScreenUpdating=True when tue sub ends

answered Nov 20 '18 at 09:47

Foxfire And Burns And Burns

10,665
2
17
38

2

I didn't downvote, but the OP refers to screen updating not making any difference, oddly. – SJR Nov 20 '18 at 13:29
*screen updating to false but it doesn't really speed things up* Absolutely right. I did not read the question properly. My bad. – Foxfire And Burns And Burns Nov 20 '18 at 14:39

How can I make my code faster? (Copying columns)

2 Answers2