Distinct combinations using autofilter (non-contiguous range)

[dchubbock]

There is a raw data sheet from which i am outputting distinct (unique) values on a separate sheet for a number of columns. This is working fine for one column with code like this;

Sheets("Raw Data").Range("A1:A5000").AdvancedFilter Action:=xlFilterCopy, _
CopyToRange:=Sheets("Uniques").Range("D3"), Unique:=True


However, i would like to output distinct combination from two columns that are not next to each other. Example below is columns D & F Here is the code i've tried:

Sheets("Raw Data").Range("D1:D5000,F1:F5000").AdvancedFilter Action:=xlFilterCopy, _
CopyToRange:=Sheets("Uniques").Range("F3:G3"), Unique:=True


The error: Table range is not valid. Is it possible to overcome this without re-arranging the column order?

[stnkynts]

You might not understand what is going on here so let me know if you have any quesitons.

Please Login or Register  to view this content.

[dchubbock]

Thank you for your response. Looks like a completely different approach, and you're right i don't really know what is going on!

I am seeing an error when running this:

"Run time error 6

Overflow"

The Debug highlights this line:
For i = LBound(rng1, 1) To UBound(rng1, 1)

Do you know how to fix this? Thank you for your help

[stnkynts]

Submit your workbook. I do not get an error.

[dchubbock]

I have it working now thanks - i think it was trying to paste over existing values which it didn't like.

One minor adjustment, if you are able to help further, I would like it so that each time the macro runs it replaces anything that existed from f3:g3 downwards. Appreciate your help

[dchubbock]

I guess sticking this at the beginning would do the trick:

Sheets("Uniques").Activate
Range("F3:G3").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Clear

[stnkynts]

I guess sticking this at the beginning would do the trick:

Yes, that is one way