How can I loop through a filtered table and check if each row is in another sheet using vba?

Question

I have 2 worksheets. Lets call them worksheet A and worksheet B. In worksheet A I automatically pull in a huge amount of data and apply the proper filters to narrow down to the critical data (see image)

this next part is where I need help:

loop through only the visible rows in sheet A
check if the first column matches any row in the first column of sheet b
if it does match then I need to check that the second column in that same row in sheet A also matches the second column in the matched row in sheet B.
if it passes both those test then I need to copy the whole row and append it to the end of the data in row B.

so far I have:

    with ThisWorkbook.Sheets(sheet_name) 
    Dim open_package As Range
    Set open_package = ThisWorkbook.Sheets("Open Packages").Range("A2", Range("A" & Rows.Count).End(xlUp))
    Dim rng3 As Range



    Dim rng_package As Range
    Set rng_package = Range("A1", Range("A" & Rows.Count).End(xlUp)).SpecialCells(xlCellTypeVisible)
    Dim cl_package As Range


    For Each cl_package In rng_package.Rows
        For Each rng3 In open_package
            MsgBox rng3
            Debug.Print (cl_package.Cells(1) & " " & cl_package.Cells(2))
        Next rng3
    Next cl_package
End With

I am trying to troubleshoot the nested loop to see exactly in what way is it looping through everything.

Why don't you create a column C with `c2: =A2&"-"&B2`. on sheet A and sheet B.Then you have only one test and can compare the values easily. `MATCH`, `INDEX` and `VLOOKUP` will do the rest! — simple-solution, Feb 23 '19 at 13:18

score 1 · Answer 1 · answered Feb 22 '19 at 22:44

1

Ivan!

I made a code to loop only visible rows from first table and lookup them in second table, when you find a match between Transaction and Package you append a line to second table

Sub LoopALL()

  Dim i As Long
  Sheets("A").Select
  Range("A1048576").End(xlUp).Select 'selects the last visible row
  LVR = ActiveCell.Row + 1

  While Cells(LVR, 1).EntireRow.Hidden = True
      LVR = LVR + 1
  Wend

  'End of Table, first row blank
  EoT = LVR

  For i = 2 To EoT
  Sheets("A").Select


      If Cells(i, 1).EntireRow.Hidden = False Then
        TRS = Cells(i, 1)
        PCK = Cells(i, 2)
        STS = Cells(i, 3)
        DES = Cells(i, 4)


        Sheets("B").Select
        Z = Range("A1048576").End(xlUp).Row

        For x = 2 To Z
          If Cells(x, 1).Value = TRS And Cells(x, 2).Value = PCK Then
             Cells(Z + 1, 1).Value = TRS
             Cells(Z + 1, 2).Value = PCK
             Cells(Z + 1, 3).Value = STS
             Cells(Z + 1, 4).Value = DES
          End If
        Next


      End If

  Next


End Sub

answered Feb 22 '19 at 22:44

Rodrigo Moraes

114
1
8

You may want to [avoid select](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) – cybernetic.nomad Feb 22 '19 at 22:47
@rodrigo Moraes, Thank you for your help. The code works but it iterates through every single row to check if its visible or not. My worksheet A has thousandths of rows so its very time consuming and sometimes crashes. Is there any way to optimize this? – Ivan Gutierrez Feb 28 '19 at 18:39
@Ivan Gutierres. Maybe we can try another approach, but I have a view on this. 1st way: we can speed up processing turning off calculation, screen updates, events and etc while we doing the code. 2nd way: We can add a sequencial col flag your filter in an auxiliar column, sort it to reduce processing, and sort again composing your table – Rodrigo Moraes Feb 28 '19 at 19:43
But what I truly recommend is to insert a table and start to use ADO to pass SQL Instructions like an Access, so you can make a recordset and treat what you want. In fact using Excel your table can't be bigger than 1.048.572, so the processing is relatively fast – Rodrigo Moraes Feb 28 '19 at 19:46

How can I loop through a filtered table and check if each row is in another sheet using vba?

1 Answers1