Large Data VBA, Conditional, Multiple Criteria Copy Row and Paste into Sheet at Speed

[InfrequentVisitor]

Hello,

Using 2010 Excel, I have 192k rows and 6.5m filled cells of data that I need to be able to match certain criteria to and pull the matching rows from the data sheet into another sheet. It also needs to be as fast as possible (no looping?).

My research took me to these posts regarding AutoFilter:

Help Need VBA Code to copy rows to a new worksheet based on criteria
excel - VBA copy rows that meet criteria to another sheet - Stack Overflow

I've never dealt with AutoFilter before, but I did manage to get the code from the Mr. Excel post to match and copy rows to another sheet; however, my attempts to manipulate the code for my purpose resulted in only copying over the header of my table. I believe the AutoFilter codes in the linked forums only apply to a single column and I need to match within multiple columns.

Below is the criteria that a user inputs to determine how data is pulled.

Book1
	A	B	C	D	E	F	G	H	I	J	K
1	Year	Month	Created	Area	Section	Account		Purchase Order Number	Purchase Requisition Number	Project Number	Task Number
2	2017	7	7/27/2017	MATSF	4611AA
Inquiry

- Columns A2:F2 should be the default look-up method.
----- A2:F2 should build upon each other to narrow the scope of the results.

- Columns H2:K2 should be independent of each other and A2:F2.
----- If H2 has a value, the look-up ignores all other criteria; same for I2, J2, or K2.

- All the criteria have their own column in the data table, Table1.

- The result is copied as entire rows from Table1 on the "Data" sheet and pasted into the "Inquiry" sheet where Table2's 1st row starts (cell A13).
----- I assume using a table is the easiest way to maintain a filter when posting the data.

- B2, D2, and E2 are lists, with D2 & E2 cascading lists, if it matters. I can change, if needed.

- I already coded deleting the result when executing a new inquiry, so no need for that.

Thank you for the help.

Respectfully,

Eric McDermott

 

[InfrequentVisitor]

Solved.

This is the solution I used. It's quite fast considering the amount of data. I completely re-did how my inputs were arranged. I didn't actually need to have "IF" statements to prioritize a H2:K2 cell over any other input.

    Range("Table1[[#All],[Module Name]:[Month]]").AdvancedFilter Action:= _
        xlFilterCopy, CriteriaRange:=Sheets("Inquiry").Range("A1:E2"), CopyToRange _
        :=Sheets("Inquiry").Range("A17"), Unique:=False

- Eric