Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Find rows that have a cell value that match a value in a named range.


I would like to filter/preserve all of the rows with in a range that contain a? value in column E that mathes a value in a named range. I would like all of the rows that have a cell in cloumn E which matches any of the values in the named range to be copy and pasted to a new worksheet. Thank you for reading.



Can you confirm : You have 2 named ranges. Lets call them Data Range an Selection Range. Column E is in the Data Range.
Values in Column E may or may not be in the Selection Range.
When the value in coloumn E is found in the selection range the row in the data range (or the entire row?) needs to copied to new worksheet.
Do you want a new worksheet for each item found in coulmn E or can all the selected rows be copied to the same new worksheet.
Or should there be a worksheet for each of the unique entries in the Seletcion range for example.
If your seletion range has Boy, Girl, Man, Woman do you want all the Boy Gir Man Woman entries on the same new sheet or all the Boys on one, girls on another etc.
Please let us know which of these you want. What you have done so far and what little bit you are haveing problems with.
k1w1sm (rep: 197) Jun 21, '18 at 7:03 pm
Thank you for your response. You correctly described the environment. The data range doesn't have to be a named range; I was just going to find the last row and loop my way through. All of the rows that have meet have a cell in column E that matches ANY of the values in the Selection Range (which is just a single column of 2-digits state postal codes) will be copied and pasted to a new worksheet. And yes, Boy, Girl, Man or in my case, NY, AL, AK, MD, etc.. are all pasted in consecutive rows (sort doesn't matter) on the same newly created results worksheet. Thank you for your questions and offer to help.

I can loop through a data range and find rows that have a single specific value, but do know how to loop through and look for more than one value without listing all of the values in th selection range.?
TonyL (rep: 2) Jun 21, '18 at 7:18 pm
I would do this by looping down the data sheet and using the value in column E to do a "find" in your selection column. If there are no blanks in col E just loop while not null. Unfortunately it will be a few days before I get back to excel but if you like I can mock one up for you. It is good to see different ways to do stuff
k1w1sm (rep: 197) Jun 23, '18 at 6:50 am
Add to Discussion


Selected Answer

Please try the code in the attached workbook. On the ribbon's?Developer tab click Macros, select CreateFilteredList and press Run. The macro will create a sheet with the rows from the Data tab specified in the NamedRange range and call it Result.

To import the macro into your own workbook just drag the entire code module FilteredList into your own VBA project in VBE's Project Explorer while both workbooks are open. You can also copy the code and paste it into a blank code module in your project?(the name isn't significant). There are instructions for adapting the code to your own encironment at the top of the macro. All changes are contained within 5 constants, fully explained and easy to understnad. Let me know if you need more help.



Thank you. I am workng on making it suit my workbook. Much of this is over my head, but I guess that is how one learns. I can't thank you enough for your time and expertise regardless of the outcome. I will mark answered as soon as I try it out. Many thanks and much peace.
TonyL (rep: 2) Jun 22, '18 at 11:42 am
Add to Discussion

Answer the Question

You must create an account to use the forum. Create an Account or Login