CODEWITHSUNDEEP
vbaexcel
LianaP
LianaP

Reputation: 1

VBA: Copy entire row, based on id, referenced by another row's cell "id"?

General excel / VBA question: I have a cell (C3) that references a value ("1"), found in another cell & column(B2). How can I copy the entire row (B) that contains the cell referenced?

My example I have a macro for a table that I advance filtered based on multiple columns & criteria, and then I copy to a new sheet the relevant rows.

Please see simplified table below. (Please note "Copy" column is simplified version of advance filter criteria & results, for illustration purposes.)

Using my code, it would copy the rows "Bread", "Apples", "Pears"".

Where I get stuck: I need to also copy the row that contains the Parent ID, only if the child row meets the advance filter criteria ("Copy": Y). So I would also need to copy the entire row for "Grains" and "Fruit".

But not "Drinks".

And "Fruit" should only be copied once. (even if there are two rows "Apples" "Pears" that match the id "2", and the advanced filter criteria).

Please note: I cannot use or reference the "Copy" column, since this is only used for illustration purposes. 

Details     ID  ParentID    Copy
*GRAINS*    1       
Bread           1            Y
Rice            1   
*FRUIT*     2       
Apples          2            Y
Pears           2            Y
*DRINKS*    3       
Juice           3            N
Milk            3            N


Sheets("Configuration").Range("B7:Y259").AdvancedFilter _ 
Action:=xlFilterCopy, CriteriaRange:=Sheets("Filter").Range("A1:H8"), _ 
CopyToRange:=Range("A1"), Unique:=False

Where my filter range is this:

Summary Summary TPM Senior Config Config - India QA - India Integration Engineer QA - Van <>Total >0 <>Total >0 <>Total >0 <>Total >0 <>Total >0 <>Total >0 <>Total >0

Upvotes: 0

Views: 949

Answers (1)

Michael
Michael

Reputation: 538

What you want is not directly possible with advanced filter.

I think your best bet would be to adjust your criteria such that all parents are copied.

Then in your filtered dataset, filter out the empty Parent ID's and delete those rows.

Upvotes: 0

Related Questions