Is it in tabular format? If it is then you can sort your data by the #N/A's and copy the range to an alternate sheet.
So I have a spreadsheet that contains the string "N/A" in multiple cells. This string signifies data that is missing and will eventually need to be populated. How can I search for each instance of "N/A" then capture the entire row that it appears in and copy it somewhere?
Ideally, I'd like to copy each row that contains "N/A" to a separate workbook. Any guidance would be most appreciated.
Thanks.
Is it in tabular format? If it is then you can sort your data by the #N/A's and copy the range to an alternate sheet.
Ken Puls, FCPA, FCMA, MS MVP
Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training), with my book M is for Data Monkey, or our new Power Query Recipe cards!
Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter
If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.
Right, that's actually what the main thrust of the tabular format question was directed at. As soon as you get into a table format with headers, you can sort and turn on autofilter. Personally, I would have used Autofilter myself.
One thing to be aware of with autofilter though... if you have a ton of non-contiguous rows, sometimes the copy method will fail because the selection is too complex. If that happens... sort your data first. That way when you autofilter the range you want to select is contiguous (grouped together).
![]()
Ken Puls, FCPA, FCMA, MS MVP
Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training), with my book M is for Data Monkey, or our new Power Query Recipe cards!
Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter
If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.
Excellent insight! Thanks again for your reply.
Bookmarks