Filter table by multiple criteria and delete all irrelevant rows

Hi Experts,

Maybe you can help me find a good solution for this need:

I have 2 defined excel tables with dynamic ranges:
1) First table - The main one that I need to filter one of its columns by multiple criteria and then delete all irrelevant rows.
2) Second table - Uses as an index, containing the values to filter the specific field accordingly in the first table.

The required field for filtering in the first table called 'Company', and its column contains multiple companies names. Let's say: AAA, BBB, CCC... --> ZZZ.
The second table, contains a reduced company names list, that the main table should be filtered on them only. Let's say: AAA, FFF, GGG, MMM, RRR (this list can be changed).
All rows with other company name that are out of the filtering criteria should be deleted from the first table.

I'm looking for an optimal VBA code (Autofilter? Loop?) to filter the 'Company' column in the main table with the values indicated in the index table (use them as a dynamic array) for the filtering action, and then delete all irrelevant rows (those who were hidden).
Note that both tables are with dynamic range.

Will be happy to get a suggestion.

Thanks in advance

Hi,

as a suggestion, it depends first on how many rows contains the data table :

• for not too many, you can filter directly for the undesired rows via for example a formula used by an advanced filter
  (or directly via a classic filter, whatever but just using Excel basics easier to maintain for a beginner)
  in order to have only the rows to delete the visible ones, all rows to keep hidden …
  
  
• For big data, faster than deleting rows may be clearing rows !
  Use a formula in a helper column where result is FALSE for rows to keep and TRUE for the irrelevants to delete.
  Sort the table on this helper column in order all the TRUE rows are moved to the end of the table
  then it's easy to clear the TRUE contiguous rows at once then clear the helper column …
  Even operating this way manually takes a maximum of a single minute,
  far faster than any bad code looping rows for ages and deleting the irrelevants one by one !

Hi Marc,

Thanks a lot for your answer and perspective.

The data table contains around 12,000 rows and it can be increased or decreased from time to time.
This Excel file I got, requires a lot of manual work by the users and I'm trying to build a set of macros to automate the entire manual process, so it will be much more efficient.

My request here is only a small part out of the whole macros sequence, containing many other automated actions and manipulations.

I really want to add this filtering/deleting action as part of the macros sequence, and not leave it to user to do it manually...


Can you please provide some example for a VBA code or formula to filter on multiple items, and delete irrelevant rows for this kind of data volume?

Thanks a lot

For more than 10K rows the better way is clearing rows rather than deleting,
a sample among others in the post #10 of this thread Delete row if cells in columns A to D are <0 …

For the first way you can activate the Macro Recorder and operate manually in order to get a code base.