VBA Fastest way to filter for 2 specific conditions-CodePudding

I have a sheet called gar_nv of 2553 rows and 135 named columns. My code looks for a row containing 2 values: produit and acte, and pastes the variable Remb in the appropriate "Col" column. I'm wondering if there's a faster way to do this ?

For Line = 1 To gar_nv.Cells(Rows.Count, 1).End(xlUp).Row
                'produit, acte, Col and Remb are variables defind above
     If gar_nv.Range("GCGAR6").Rows(Line).Value Like produit Then
           If gar_nv.Range("GCBARB").Rows(Line).Value Like acte Then
              gar_nv.Range(Col).Rows(Line).Value = Remb    
           End If
      End If
Next Line

CodePudding user response：

You could write the ranges to variant arrays, as iterating through those would be much faster, e.g.

Dim GCGAR6, GCBARB
GCGAR6 = Range("GCGAR6").Value2
GCBARB = Range("GCBARB").Value2
.....
For Line = 1 To gar_nv.Cells(Rows.Count, 1).End(xlUp).Row
                'produit, acte, Col and Remb are variables defind above
    If InStr(1, GCGAR6(Line, 1), produit) Then
        If InStr(1, GCBARB(Line, 1), acte) Then
              gar_nv.Cells(Line,Col).Value2 = Remb    
        End If
    End If
Next Line