Use Advanced Filter in VBA to copy unique values to other sheet

Hi everyone,
i am working on a macro which takes values from sheet 1 and paste unique values in sheet2.

I have written code for this but the problem i am facing is that it generates first value twice.


	VB:
	
 Range 
Set r = Range("H8", "H240") 
 
Set r1 = Range("H8", Range("H8").End(xlDown)) 
r1.Sort Key1:=r1, Order1:=xlAscending, Header:=xlNo 
 
r.AdvancedFilter _ 
Action:=xlFilterCopy, _ 
CopyToRange:=Sheets(3).Range("H8:H100"), _ 
unique:=True 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
This macro works fine but the value at H8 gets repeated twice.

Please help m stuck at this point.

THanks in advance.


I was trying to Generate unique values from a set of values.

I have written this code for that.


	VB:
	
 MakeUniqueList() 
    Dim lasth As String 
    lasth = "h" & lastrow() 
    Dim r As Range, r1 As Range 
    Set r = Range("H4", lasth) 
    r.AdvancedFilter _ 
    Action:=xlFilterCopy, _ 
    CopyToRange:=Sheet3.Range("H4:H1000"), _ 
    UNIQUE:=True 
     
     'Range("H4").Delete Shift:=xlShiftUp
    Set r1 = Range("H4", Range("H4").End(xlDown)) 
    r1.Sort Key1:=r1, Order1:=xlAscending, Header:=xlNo 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
The problem i am facing is that it sorts the values from the list but i dont know why its printing the first value twice.

Please let me know if that can be done by some way.

Thanks in advance

Hi, I am trying to use advancedfilter in VBA to copy unique values from a column in one sheet to a column in another sheet.
The sheet and range with the non-unique list I am copying from is "Sheet1" and range BA4-BA5000
The sheet and range I am copying to (which should only contain unique values from sheet1) is called "Sheet2" and range A102 onwards.
My VBA code is as follows:

	VB:
	
 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Here is a sample of the data in sheet1:

Here is the complete output in sheet2:

Note that D4T-3TC-EFV is listed twice despite the Unique:=true bit in the code. Just to be sure it was a duplicate, I tried this in the VBA immediate window:

	VB:
	
?Sheets("Sheet2").Range("A102")=Sheets("Sheet2").Range("A103") 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
and it evaluated to "True"

Any suggestions to get unique only results from sheet1 into sheet2 will be greatly appreciated. Where did I go wrong? (I have read the advancedfilter help on the site but can't see where I went wrong).
Thanks! Auto Merged Post;

Just to add to my confusion about the advancedfilter...
If my source range (Sheet1 cells BA4-BA5000) start with anything other than "D4T-3TC-EFV", I get this error:

I tried simply copying a cell in the BA column with another value ("ZDV-3TC-NVP") into BA and got the above error. Since it is a text cell why does the advancedfilter care what's in it?

Thanks in advance!

I've built a MASSIVE Excel macro for one of our ICP-MS instruments. The very last part of the app is a reporting function, and the last piece of THAT is to search a column that has LIMS record numbers in it and create a comma-separated list of the distinct LIMS numbers. The problem is that when I run the following line of code against the following values, it returns the following values.

Cell Value B13 205463 B14 205463 B15 205463 B16 205463 B17 205463 B18 205463 B19 206121 B20 206121 B21 206121 B22 206121 B23 206121
returns

Cell Value AA6 205463 AA7 205463 AA8 206121
when I run the following code


	VB:
	
 TryAgain() 
    Range("B13:B23").AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range("AA6"), Unique:=True 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

I am at a LOSS! No matter what I do, the code returns two instances of whatever the first value is that it finds more than one instance of. If I switch the 206121 values with the 205463 values, and put the 206121 values at the top, the code returns 206121, 206121 and 205462. Can ANYONE tell me what I'm doing wrong? I've cleared the cells and manually entered values to make sure there is not a stray space or other character that IS making two values distinct that appear to be identical...completely baffled on this one. BTW, if I replace the first value (cell B13) with a null, the function returns nothing. If instead I replace the SECOND value with qa null, I get
205463

205463
206121

(The second cell down is null)

ANY suggestions?

I checked the Help area and got lots of good information about using advanced
filters in Excel. The question I still have is: How do you filter on a text
field to find words ending in a letter or combination of letters? For
example, I want a list of the records where a particular text field ends in
the letters "en".

I tried using "*en", but that also gives you words where the "en" appears in
the middle of the word.

I checked the Help area and got lots of good information about using advanced
filters in Excel. The question I still have is: How do you filter on a text
field to find words ending in a letter or combination of letters? For
example, I want a list of the records where a particular text field ends in
the letters "en".

I tried using "*en", but that also gives you words where the "en" appears in
the middle of the word.

Hi,

I am trying to migrate a model that I built in Excel 2002 to Excel 2007. The model has an advanced filter in it to get unique values. However in 2002 the filter worked correctly but in 2007 it produces nothing. The code excerpt is below :-

Worksheets("Transaction Data").Range("PCT_List").AdvancedFilter Action:=xlFilterCopy, _
CriteriaRange:=Worksheets("Transaction Data").Range("criteria"), _
CopyToRange:=Worksheets("Transaction Data").Range("Unique_PCT_List"), Unique:=True

Any ideas ? Thanks

Kaps

I have searched for three nights through Ozgrid and other forums, as well as all my Excel books, to try and find the answer to my problem. In theory, it should be ridiculously easy, but I cannot get AdvancedFilter to work in VBA! I have copied and pasted ad nauseum, but there is clearly a trick that continues to escape me.

Here is the issue: I have a worksheet, named "Invoices", with column headings: InvNum, Date, Customer ID, Qty, ProdCode, Price, Disc, Total. What I want to do is filter the entries between two dates which the user can select via a userform. These two dates are pasted to a worksheet, and I have created a named range "Criteria" relating to these two adjacent cells, containing a start and end date. I have set up a dynamic range of all entries in the "Invoices" sheet, called DynamicInvoiceRange (using Offset). Once filtered, I want to copy the results to a sheet named "StoreResult."

I am sure this sounds like VBA 101 to you, but I have now given up: I get the filter to copy the records to the StoreResult sheet, but no filtering takes place, and despite all my efforts, all records get copied every time with no filtering on the invoice dates.

What am I missing here? Any help will be most sincerely appreciated.

I wonder if this were possible:
Use MS Query to construct a SQL statement and use the statement in VBA code
(ADO, ODBC connecting to Interbase), but don't let it run the statement.
So just use the MS Query as a SQL wizard, incorporated in VBA code.
Doing this would save a lot of coding to get a similar wizard, although it
shouldn't be too difficult either.
One drawback I can see is that MS Query won't let you do a WHERE condition
on a field that is not in the SELECT
clause. The good thing though is that it will automatically add any joins,
although I am not sure how it will handle
joins that are not inner joins.
Hope this explains it clear enough and thanks for any advice.

RBS

I am try to figure out a way to use Auto-filter when some of the cells in a
worksheets are protected. I don't want users to be able to change the data in
the sheet but they need to be able to use Auto-filter in order to do some of
their work. The problem is, anytime we protect the sheet, they can't use
Auto-filter.
Is there a work around for this?

Thank you.

Hello, I cannot seem to get a simple advanced filter to work using VBA. It works fine when I perform the task manually on the same data range and criteria, but when I use the code below I get the message: " Run time error '1004' The extract range has a missing or illegal field name". My code is below:
Sheet12.Activate
Sheet12.Range("A1:R20000").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Range _
("t1:t4"), CopyToRange:=Range("AA1:AR1"), Unique:=False
ActiveSheet.Range("AA1").Select
ActiveSheet.Range(Selection, Selection.End(xlDown)).Select
ActiveSheet.Range(Selection, Selection.End(xlToRight)).Select
Selection.Copy

Any help would be much appreciated.

Thx, Wratpack

HOW TO: Use Advanced Filter to Exclude Records in Excel 2000

This step-by-step article explains how to use the ISNA and MATCH worksheet functions to exclude records in a Microsoft Excel database that do not match another list. How to Create ...

Happy New Year!

I seem to be having an odd problem with Advanced Filter. When I use the Copy to Another Location option I get an error that:

"The extract range has an illegal or missing field name".

As far as I can tell, there are no blank or missing fields. I did originally have a DATE field but I have renamed this in case it was a "reserved" name but still have the problem.

When I used the filter in place it works correctly.

I am trying to use Advance Filter as opposed to lots of looping, copying and pasting and this is the only snag. I suppose that I could filter in place then copy/paste the results to the other sheet but I'd like to see where I may be going wrong.

Thanks in advance!

Cheers,
Averil

Hi everyone!

When I use Advanced Filter to filter out Unique records and copy them to a new location, it copies only the first record and only the first column of the data.

I tried with the heading-row included and excluded but the result doesn't change.

When I apply the same strategy to another worksheet with the same headings but different data, it reports an error "The extract range has a missing or illegal field name".

I think, I am not complying with some requirements of the feature.

Please help me ,in a bit detail, so as to get me out of this problem.

I am a novoice in VBA, trying to run a macro using Advanced filter.

If someone can help me find the reason for error and means to overcoem it.

Dim wsData as Worksheet
Dim wsList As Worksheet
Set wsData = Worksheets("Data")
Set wsList = Worksheets("List")

If Target.Address = "$C$9" Then

wsData.Columns("A:Z").CurrentRegion.AdvancedFilter _
Action:=xlFilterCopy, CriteriaRange:=wsList.Range("J1:J2"), _
CopyToRange:=wsList.Range("A1"), Unique:=False
' ( system gives error on advanced filter condition)
End If
End Sub

thanks

6/6/2005 12:30 DBAB Media VIA.B
6/6/2005 14:00 DBAB Media SFA YBTVA
6/6/2005 14:30 DBAB Media CMCSA CMCSK HHS
6/6/2005 15:00 DBAB Media ADVO PIXR SGA
6/6/2005 15:30 DBAB Media VCI WON

I am trying to use advanced filter to search for a specific group of
stocks (eg. I1:I100) in columns D-F and return only the stocks on my
list. eg. PIXR is on my list so the filter would return:

6/6/2005 15:00 DBAB Media PIXR

I am having problems getting the criterion correct. Can this be done
with advanced filter or is there an easier way?

--
Potatosalad2
------------------------------------------------------------------------
Potatosalad2's Profile: http://www.excelforum.com/member.php...o&userid=20834
View this thread: http://www.excelforum.com/showthread...hreadid=377137

Hi all,

I have another question to ask, that is why I am posting another thread. I have a column with blanks and data (roughly abt 300 rows as such, not unique, there are many rows repeated numbers). How can I copy the distinct values from this column to another sheet. I am able to copy unique values to another column in the same sheet. But when I try to copy them to another sheet it gives me an error. I used advanced filter option to do this. I also want to get the count of these unique values. In sql I know it can be acheived with "Select Distinct row1, count(row1) from table1 order by row1". Can u help how to do this in excel.

Thanks a lot in advance,
Nitu

6/6/2005 12:30 DBAB Media VIA.B
6/6/2005 14:00 DBAB Media SFA YBTVA
6/6/2005 14:30 DBAB Media CMCSA CMCSK HHS
6/6/2005 15:00 DBAB Media ADVO PIXR SGA
6/6/2005 15:30 DBAB Media VCI WON

I am trying to use advanced filter to search for a specific group of stocks (eg. I1:I100) in columns D-F and return only the stocks on my list. eg. PIXR is on my list so the filter would return:

6/6/2005 15:00 DBAB Media PIXR

I am having problems getting the criterion correct. Can this be done with advanced filter or is there an easier way?

Hello, I hope some of you can help me because I'm pulling my hair out at the moment. I have a large dataset that will be presented in a pivot table which will be very adjustable through selections of cascading combo boxes. I'm comfortable with manipulating pivot tables with VBA and the combobox results but I'm not getting anywhere with creating unique lists that will cascade with combobox choices.

My data is in range A:D, this would normally have numerical data attached but I've stripped it out hence why the data may look slightly odd. I've used advanced filters through VBA which is very useful to summarise the data using parameters from the combobox (represented in range F:I) but it's keeping the data relational (range K:N) so I'm ending up with duplicates. My bright idea was to then use another advanced filter individual columns in range K:N and I should have unique values in P:S, but it's not turning out like that! Range U:X is what I'd ideally have in range P:S... The filter button triggers my basic code.

I know this should be simpler than I'm doing it but can anyone offer some help or suggestions to how I should do this? I've looked at data validation, advanced filters, vba loops but I'm still at square one. Any help would greatly be appreciated! Thanks

Hi

I am just asking whether it is at all possible using Advanced Filter with NOT and unique records? To explain my logic, this is what I am trying to achieve, I have a column called option codes, in this column there are duplicate values and values that have the word "NULL". Now, what I want to do is to filter on this column all unique values but I also want to keep all rows that have the word NULL in that column, too. I want to do this in one filter rather than having to go back and forth.

Is there away to do this?

Example output would be:

AA
BB
CC
NULL
NULL
.
.
.
.
.

Hi
I'm using the advanced filter in place to filter a list based on certain criteria and then copying the filter result into another worksheet. It works OK if there is data matching the criteria but if there is no matching data it copies the whole of the "list range".
Any help on how I can prevent this would be gratefully received.

How to Use Advanced Filter to Exclude Records

You can use the ISNA and MATCH worksheet functions exclude records in an Excel database that do not match another list. To create exclusion criteria, use the following formula ...

Can you use or apply advanced filters in a shared workbook?

I have a workbook project where I am dynamically applying advanced filters through VBA.
The intention is to share the workbook at some point for data entry among a group of people. I did not see advanced filtering listed in Excel Help as one of the features that are not available, but I did note that if I try to apply one through the menu bar it is greyed out in a shared workbook (autofilter is still available).

Hi everyone,

Background: I have a CSV datasheet that is used in an Excel Pivot table workbook.

Each week the datasheet file is refreshed and posted on a SharePoint 2007 Shared Document folder.

Question: I would like to create a VBA macro in the excel workbook to allow users to download the newest version of the datasheet from SharePoint to their hard drive prior to refreshing their workbooks.

Issue: I am a newbie to this and having difficulty in finding the correct syntax in VBA to accomplish this. MS Excel help has been used and multiple possibilities tried but I have getting stuck on the SharePoint network address.

Any thoughts?

Simple summary:
1. Create a macro in an excel workbook to copy a CSV data source from a SharePoint folder to the users hard drive.
2. SOURCE: SharePoint folder network address (ex. 'sharepoint.xxx.comteamsDeptShared DocumentsReportsPivot_Source_Data) causing failure when trying to copy xxxxxxxx.CSV file.
3. TARGET: C:ReportsPivot_Source_Dataxxxxxxxx.CSV

Thank you.

BTW, I do have the book Excel 2007 by John Walkenback but is there another beginner to intermediate book that may provide actual VBA (highly used) examples? Any recommendations are greatly appreciated

I have same data with macro for example John, John A, Smith, Eric etc..
But when i used the filter and choose John, John A always displayed by the computer.

Does anyone can help me how to display specific data using macro in advanced filter?

This is my Macro :

Range("B11").Select
Sheets("Master").Range("C12:AA25000").AdvancedFilter Action:=xlFilterCopy, _
CriteriaRange:=Sheets("Sheet3").Range("O3:O4"), CopyToRange:=Range("B11"), _
Unique:=False