Criteria in Column Y, Copying Column x but pasting unique values only in new worksheet

jaspalsd

Board Regular
Joined
Feb 3, 2014
Messages
72
Hi,

I can do this a little long-winded way but I am wondering on the most efficient way of doing the following:
  1. Go to a worksheet and then filter a column with a certain value
  2. Once filtered, select a range from another column
  3. Copy this select column and ensure values are unique

So I have found code that can filter unique values based on one column but I am unable to set a criterial within the line of code.

Code:
[COLOR=#333333]Public Sub Test()[/COLOR]
ActiveSheet.Range("A2:A65536").AdvancedFilter Action:=xlFilterCopy, CopyToRange:=ActiveSheet.Range("B2"), Unique:=True
 [COLOR=#333333]End Sub[/COLOR]


Another method is to do code using the With Statement and I am able to paste the columns to a new worksheet but it'll paste the whole range including duplicates

I guess I can just remove the duplicates after pasting to the new worksheet but out of interest can I apply criteria in the code above?

Thanks!
 
Last edited:

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.
Hi,

Managed to work a way around it

Did a With Statement relating to the table and filtering the column by the criteria. Once filtered I applied a similar formula to the above
 
Upvote 0

Forum statistics

Threads
1,213,558
Messages
6,114,297
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top