Filtering data

**PinkBanana** · 05-13-2011, 05:13 PM

Hello

I have been using Advanced Filter for a while but want to expand the filter but can't get it to work properly. I will explain:

I have a range of four columns. The criteria is in the third column and it extracts all rows that match the criteria.

I want to expand the range to eight columns. The criteria will remain in the third column but some of the data will be in column four and some in column eight.

When I run the new version, it returns data from columns one, two and four OK but doesn't return anything from column eight.

Am I doing something wrong?

**jeffreybrown** · 05-13-2011, 08:14 PM

Is it possible we could see your code? Maybe we can see the error.

Please remember to use [ code tags ] when [ / posting code ]

**PinkBanana** · 05-14-2011, 09:03 AM

Here is the original code, which has been working fine;

Please Login or Register  to view this content.

And this is the modified version;

Please Login or Register  to view this content.

Thanks

**jeffreybrown** · 05-14-2011, 10:57 AM

I don't see anything obviously wrong with your code, but on the CopyToRange argument, I would probably change it to just the first cell of the region.

Please Login or Register  to view this content.

I ran the code and it seems to run fine.

Look at the attached workbook and please tell me what is different about your setup? Maybe this will help us see what's going wrong.

**PinkBanana** · 05-16-2011, 01:38 PM

Thanks for your response. Sorry to be so long replying.

The only difference I can see is that my data is in columns D and H whereas you have a solid block of data.

Incidently, I noted your comment on CopyTo and changed my code to reflect your tip. It didn't solve the problem but the results are quite interesting and I've been playing around with it.
The criteria is in column C.
CopyTo A1 only retrieved data from the first column.
CopyTo A1:D1 retrieved data from columns A to D.
CopyTo A1:H1 retrieved data from columns A to D and pasted accordingly. It also repeated the data from column D in column H.

Interesting, but I just can't get the data I want to extract from column H.

Thanks

**jeffreybrown** · 05-16-2011, 01:50 PM

Can you post a sanitized copy of your workbook with what results you expect?

**PinkBanana** · 05-18-2011, 04:04 AM

Have attached a file called Trial copy. Hope I've done it right.

I want to extract all data from rows with 'c' in the TC column.

Thanks

**jeffreybrown** · 05-18-2011, 10:02 AM

Try this...

In cell A78 data validation which works on the worksheet change event. Just pick a new letter (a, b, or c) and the macro will fire.

You might want to reconsider some of your setup. Merged cells are not a friend of VBA. Also, with your headers, IN and OUT, I renamed them and the macro runs fine.

Let me know if you have any questions.

**PinkBanana** · 05-22-2011, 05:24 PM

Sorry for the delay in replying. I do appreciate the help I get but at the same time I am trying to learn from it, not just letting others do things for me.

Thank you. That is brill. It works fine as-is but when I copied the code into my project it didn't.

When I run it in my project it returns just blank cells with borders from cell A81 up the sheet not down as it does in the Trial Copy.

The original code was the first step in a series of actions when the button was pressed. I have removed all my original code [I am rebuilding from the bottom up anyway] but however I change it about the results are the same.

I didn't immediately understand everything you did but I think I've got the gist of it now. I have been trying different things with it in my project to see if I could get it to work but I just cannot understand what's going on.

I note your comment about merged cells. Yes I have had problems in the past but so far I seem to have got away with just referring to the first cell of the merged range. I will keep it mind.

Again many thanks for your assistance.
Mike