optimizing vba looping code

[jaik22]

Sub Testingloop()
Dim endrown As String
Dim ex As String
Dim ez As String
Dim eh As String
Dim eg As String
Dim el As String
Dim ee As String
Dim es As String
Dim ef As String
Dim ei As String
Dim i As Integer
Dim LastRowColumnA As Long: LastRowColumnA = Sheets("looping").Cells(Rows.Count, 1).End(xlUp).Row


Application.ScreenUpdating = False


Sheets("looping table").Select
endrown = Sheets("looping table").Range("I1000").End(xlUp).Row
    For i = 3 To endrown
        
        ee= Cells(i, 9).Value
        ex= Cells(i, 10).Value
        ez= Cells(i, 11).Value
        es = Cells(i, 12).Value
        ef = Cells(i, 13).Value
        ei = Cells(i, 14).Value
         eh = Cells(i, 15).Value
        eg= Cells(i, 16)
        el= Cells(i, 17)


        Sheets("looping").Select
             ActiveSheet.UsedRange.AutoFilter Field:=1, Criteria1:=ee
             ActiveSheet.UsedRange.AutoFilter Field:=2, Criteria1:=ex
             ActiveSheet.UsedRange.AutoFilter Field:=3, Criteria1:=ez
            ActiveSheet.UsedRange.AutoFilter Field:=4, Criteria1:=es
            ActiveSheet.UsedRange.AutoFilter Field:=5, Criteria1:=ef 
             ActiveSheet.UsedRange.AutoFilter Field:=6, Criteria1:=ei
            ActiveSheet.UsedRange.AutoFilter Field:=7, Criteria1:=eh
        On Error Resume Next
        Range("H2:H" & LastRowColumnA).SpecialCells(xlCellTypeVisible).Value = eg
        Range("I2:I" & LastRowColumnA).SpecialCells(xlCellTypeVisible).Value = el
            ActiveSheet.ShowAllData
                Sheets("looping table").Select
                
                   Next i
                   
End Sub

I have for loop code like this to fill out the looping sheet based on the looping table sheet data.
So my code loop through field 1~7 to put eg, and el data in the looping worksheet. Is there any good way to make this code faster?

Thank you!

 

[StephenCrump]

You should get some speed improvement by:
- reading your filters once into a VBA array, rather than cell by cell
- getting rid of the unnecessary .Selects

Sub Testingloop1()

    Dim vFilters As Variant
    Dim llastrow As Long, i As Long, j As Long
    
    llastrow = Sheets("looping").Cells(Rows.Count, 1).End(xlUp).Row
    With Sheets("looping table")
        vFilters = .Range("I3:Q" & .Range("I1000").End(xlUp).Row).Value
    End With
    
    Application.ScreenUpdating = False
    
    With Sheets("looping").UsedRange
        For i = 1 To UBound(vFilters)
            .AutoFilter
            For j = 1 To 7
                .AutoFilter Field:=j, Criteria1:=vFilters(i, j)
            Next j
            On Error Resume Next
            .Range("H2").Resize(llastrow - 1).SpecialCells(xlCellTypeVisible).Value = vFilters(i, 8)
            .Range("I2").Resize(llastrow - 1).SpecialCells(xlCellTypeVisible).Value = vFilters(i, 9)
            On Error GoTo 0
        Next i
        .AutoFilter
    End With
    
    Application.ScreenUpdating = True
                   
End Sub

But if you have many lines of filters, it may be faster to use a formula approach:

Sub Testingloop2()

    Dim llastrow As Long
    
    llastrow = Sheets("looping table").Range("I1000").End(xlUp).Row
        
    With Sheets("looping")
        With .Range("H2:I" & .Range("A" & Rows.Count).End(xlUp).Row)
            .Formula = "=IFERROR(INDEX('looping table'!P$1:P$" & llastrow _
            & ",MAX(IF(MMULT(--($A2:$G2='looping table'!$I$3:$O$" & llastrow _
            & "),{1;1;1;1;1;1;1})=7,ROW('looping table'!$I$3:$O$" & llastrow & "),-1))),"""")"
            .FormulaArray = .FormulaR1C1
            .Value = .Value
        End With
    End With
                       
End Sub