excel - Loop in Columns and Rows to find and copy values VBA

Question

good morning!

I'm new here, new in VBA and my english isn't that good so, please, be patient with me =D

I need to create a multi loop for the table below in the image. This is the logic of the loop:

The loop need to find and copy all the rows where (for instance): If Bat=1 and Bet=1 and Bit=F and Bot = 1 and But=1, copy all the rows that meet this condition and create and paste the whole row in another sheet.

It needs to be dynamic because despite the structure of the table will always be the same, the data changes everyday, and I will need to create one loop for each "categorical" row, for instance: 1-1-F-1-1, 1-1-J-1-1 and so on.

I really need your help to build just one, because I only know how to do simple loops. I searched in a lot of topics here and I didn't find any that could help me.

Thanks and have a great day!

I tried this way but it didn't work:

Option Explicit

Sub Test()

Dim ws As Worksheet: Set ws = ThisWorkbook.Sheets("base")
Dim ARow As Long, BRow As Long, CRow As Long, DRow As Long, ERow As Long, MyCellA As Range, MyCellB As Range, MyCellC As Range, MyCellD As Range, MyCellE As Range, Names As String
ARow = ws.Range("A" & ws.Rows.count).End(xlUp).Row
BRow = ws.Range("B" & ws.Rows.count).End(xlUp).Row
CRow = ws.Range("C" & ws.Rows.count).End(xlUp).Row
DRow = ws.Range("D" & ws.Rows.count).End(xlUp).Row
ERow = ws.Range("E" & ws.Rows.count).End(xlUp).Row

For Each MyCellA In ws.Range("A2:A" & ARow)
    For Each MyCellB In ws.Range("B2:B" & BRow)
        For Each MyCellC In ws.Range("C2:C" & CRow)
            For Each MyCellD In ws.Range("D2:D" & DRow)
                For Each MyCellE In ws.Range("E2:E" & ERow)
                        If MyCellA = 2 And MyCellB = 3 And MyCellC = "F" And MyCellD = 1 And MyCellE = 1 Then
                            MsgBox "Category 1 Achieved!"
                        End If
                Next MyCellE
            Next MyCellD
        Next MyCellC
    Next MyCellB
Next MyCellA

End Sub

question from:https://stackoverflow.com/questions/65647433/loop-in-columns-and-rows-to-find-and-copy-values-vba

Answer 1

You only need a single loop here - take each row in turn and run your checks on that row.

Like this:

Sub Test()

    Dim ws As Worksheet
    Dim ARow As Long, rw As Long
    
    Set ws = ThisWorkbook.Sheets("base")
    ARow = ws.Range("A" & ws.Rows.Count).End(xlUp).Row
    
    For Each rw In ws.Range("A2:E" & ARow).Rows
        
        'Test the relevant cells on the row for the required values
        If rw.Cells(1).Value = 2 Then
            If rw.Cells(2).Value = 3 Then
                If rw.Cells(3).Value = "F" Then
                    If rw.Cells(4).Value = 1 Then
                        If rw.Cells(5).Value = 1 Then
                            MsgBox "Category 1 Achieved!"
                        End If
                    End If
                End If
            End If
        End If
           
    Next rw

End Sub

Note if you just AND all the check together it will be noticeably slower, since all 5 cells need to be read even though the first one may have failed your criteria.