Using array to make 'for' loop more efficient

**rynofrowan** · 10-31-2014, 04:47 AM

hi all!

i have a workbook (redacted) attached that:

1. takes user input on Sheets("Input")

BK = Range("B1").Value
ST = Range("B2").Value

2. takes list of trades from Sheets("Data")

3. pastes list on Sheets("Output") then loops through and deletes irrelevant trades (deleting rows where column A != BK OR left(3) of column C != ST)

Worksheets("Output").Activate
    Set SRng = Range("A1:A" & Cells(Rows.Count, 1).End(xlUp).Row)

    For i = SRng.Rows.Count To 1 Step -1
        If Cells(i, 1) <> BK Or Left(Cells(i, 3), 3) <> ST Then Rows(i).Delete
    Next i

so we only want to be left with trades that match both BK and ST

there are typically ~62k rows of trades and the loop/delete row process is very slow...i've read about ways to increase the efficiency of this process using an array but don't quite understand how to execute this. any pointers?

cheers

sampleSS.xlsx

**sweep** · 10-31-2014, 05:05 AM

Hi,

Perhaps you could do this by filtering the "Data" sheet before copying and pasting..

Sub Macro1()
Dim BK As Variant, ST As Variant
Dim rFilterRange As Range

With Sheets("Input")
BK = .Range("B1").Value
ST = .Range("B2").Value
End With

Sheets("Data").Select
Set rFilterRange = Sheets("Data").Range("A1:G" & Cells(Rows.Count, 1).End(xlUp).Row)
    
    rFilterRange.AutoFilter Field:=1, Criteria1:="=" & BK
    rFilterRange.AutoFilter Field:=3, Criteria1:="=" & ST & "*"
    
    ActiveSheet.UsedRange.Select
    Selection.Copy
    Sheets("Output").Select
    Range("A1").Select
    ActiveSheet.Paste
End Sub