Is there a faster way to do this than autofilter?

[Asator]

Basically I've got a macro that takes a file exported by SAP, calculates the price changes based on the bills of materials stored in a flatfile db in excel, then updates the SAP export file to be re-imported to SAP. That all works swimmingly. I also added a little feature to add some transparency, copying the parts updated and the commodity price changes applied to each line in the bill of materials that is used to calculate the net delta. The way I'm doing it right now, is as the parts are updated, it adds them to an array with plant # and part #. It then loops through them performing an autofilter on the BOM data, which copies the filter results to a new sheet. This works. It's also fairly^H^H^H^H^H very slow on big files (>250 records).

Is there a faster way of extracting entries that have a varying number of records based on two criteria? Or am I stuck using AutoFilter?

Relevant code:

'bfrng is the autofilter area, bfilesheet is the sheet containing bfrng, af_cp_rng is the autofilter range results, showwk is the sheet where I'll be showing work, wk_rows is the number of rows of data in the show work sheet.
For y = LBound(uniquePNPL, 2) To UBound(uniquePNPL, 2)
        wk_rows = showwk.Cells(Rows.Count, 1).End(xlUp).Row
        With bfrng 
            .AutoFilter field:=1, Criteria1:=uniquePNPL(1, y)
            .AutoFilter field:=2, Criteria1:=uniquePNPL(0, y)
        End With
        With bfilesheet.AutoFilter.Range
            On Error Resume Next
            Set af_cp_rng = .Offset(1).Resize(.Rows.Count - 1, 5).SpecialCells(xlCellTypeVisible)
            On Error GoTo 0
        End With
        If af_cp_rng Is Nothing Then
            GoTo Skip
        Else
            If af_cp_rng.Rows.Count > 0 Then
                af_cp_rng.Copy
                With showwk
                    .Range(.Cells(wk_rows + 1, 1), .Cells(wk_rows + 1, 1)).PasteSpecial (xlPasteValuesAndNumberFormats)
                End With
            End If
        End If
Skip:
bfrng.AutoFilter
    
Next y

 

[Asator]

Bump!

 

[Asator]

Bump!