Help in an iterative macro to copy/skip row based on cell value

**rhiminee** · 03-30-2012, 08:30 AM

Hi,

I have a list of customer contacts that are both active and inactive (indicated by column A as "Y" for Inactive or "N" for Not Inactive) in Sheet1. I'm trying to find a way to have Excel do an iterative query on the value in column A such that if the cell value for A2 = N, it will copy the associated row of data into another worksheet, Sheet2. If A2 value = Y, then skip that row, and perform the query on the next row, A3 to see if the value is N or Y.

Complications:
- All my data in this worksheet are linked from different files in the same directory as they are dynamic and liable to change, so I don't want to move, re-organize, manually manipulate any columns
- I'm working with +14000 rows of data, so something automated (formula? macro?) would be required

I've used IF() to return data in my Sheet2, which does only filter the active customers, but then for all rows where the value in column A = "Y" only show up as blank rows. I need a block of data without any gaps between them. Is there any way to do this? Any help would be extremely appreciated! I'm not well versed in VBA, but would be willing to experiment and learn if I had some guidance!

Thanks!

**Fraenk** · 03-30-2012, 08:47 AM

If I understand you correct, the easiest solution would be to use MSQuery.

(I have a German Client, so I'm kinda guessing what it's called in English)

Go to Data->Other Sources->Microsoft Query

Source -> Excel
File -> YourFile.xls
Sheets -> Sheet1->Content(column A-Z)
Filter -> Here you set to check if the data in A = Y (or N)

Select Sheet2 for the output, and voila, you'll have all your data filtered by column A

*edit*
always mess up row/column

**rhiminee** · 03-30-2012, 10:45 AM

Hi,

Thanks for your suggestion... I never knew about MSQuery or any of its functions! However, when I tried to follow your directions, I got the following error: "This data source contains no visible tables." I can't select any sheets or do any filtering.

Any other suggestions?

**Chippy** · 03-31-2012, 09:11 AM

Try the Advanced Filter, specifying the 'Copy to another location' option. You will need to set up the criteria range somewhere on a sheet.

**Fraenk** · 04-01-2012, 04:36 AM

Originally Posted by rhiminee

"This data source contains no visible tables."

I got the same error once. It happened to me, because the sourcefile was a protected worksheet, just unprotect it and it should work again.
If all fails, copy the data into a new file and try again.

Never used Chippy's suggestion, maybe that would be easier, you could also give that a try as well.

*edit*

**rhiminee** · 04-13-2012, 03:08 PM

The worksheet was never protected... but it still doesn't work. I'm trying to minimize copying data to another location because if I can get this working, I will want to have another separate excel file be driven off of data from this one. Are there any other possible suggestions that I can try?