Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
AdvancedFilter syntax
Currently, I am using:
Sheet3.Range("A1:A10000").AdvancedFilter _ Action:=xlFilterCopy, _ CopyToRange:=Sheet2.Range("A1"), _ Unique:=True This generates a list of the unique values from my source list. While I can get to my real end result through several additional steps, I suspect that I'm underutilizing AdvancedFilter, and am hoping for a few pointers on how to get it to return some more specific information. Specifically, (1) I do have a criteria that I'd like to use without placing it in a cell range (Select unique values from A1:A10000 where the corresponding value in Column K is '4'). It appears that I can put the value 4 in a cell and reference it, but can I just use a variable which is =4? If so, what is the appropriate syntax? e.g., CriteriaRange:= (Sheet1.range(K:K).value = 4) (2) Ultimately, what I really want is a count of the unique values that meet my criteria, so can I use a Count against the returned range, without sending it to a physical page location? Perhaps I can make a named range that references an array instead of a range, and use that to generate a count in a second step? Initially I was using formulas to get my unique counts, but using a sumproduct formula (actually, several dozen of them) against an array this large was just taking too long, and initial tests with AdvancedFilter indicated it would be much faster. I appreciate any syntax hints with the AdvancedFilter, or even confirmation on whether or not what I'm attempting is even possible with AdvancedFilter. My backup option is to just loop the page and sort the data into a 2D array (while keeping track of whether each value is unique), but AdvancedFilter seemed like a more direct option (no loops, etc) Thank you, Keith |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
AdvancedFilter syntax
Hi Keith,
I can't specifically answer your question and I will monitor the thread for answers. However, the following could be used as a work around if you don't get a better answer. It involves using a temporary worksheet for data but the temp sheet can be added as required and then deleted. Basically it is using AutoFilter for your column K value, Copy the visible data to a temp location then AdvancedFilter for the unique list. Tested in xl 2002 and xl2007. Sub Test() With Sheet3 If .AutoFilterMode = False Then .Range("A1").AutoFilter Else If .FilterMode Then .ShowAllData End If End If 'Field 11 is column K .AutoFilter.Range.AutoFilter _ Field:=11, Criteria1:="4" End With Sheets("Temp").Columns(1).Clear 'Any previous data 'Copy visible data to Temp sheet Sheet3.AutoFilter.Range.Columns(1) _ .SpecialCells(xlCellTypeVisible).Copy _ Destination:=Sheets("Temp").Range("A1") Sheet2.Columns(1).ClearContents 'Any previous data 'copy unique data to Sheet2 Sheets("Temp").Columns(1).AdvancedFilter _ Action:=xlFilterCopy, _ CopyToRange:=Sheet2.Range("A1"), _ Unique:=True 'Turn off Autofilter Sheet3.AutoFilterMode = False End Sub -- Regards, OssieMac "ker_01" wrote: Currently, I am using: Sheet3.Range("A1:A10000").AdvancedFilter _ Action:=xlFilterCopy, _ CopyToRange:=Sheet2.Range("A1"), _ Unique:=True This generates a list of the unique values from my source list. While I can get to my real end result through several additional steps, I suspect that I'm underutilizing AdvancedFilter, and am hoping for a few pointers on how to get it to return some more specific information. Specifically, (1) I do have a criteria that I'd like to use without placing it in a cell range (Select unique values from A1:A10000 where the corresponding value in Column K is '4'). It appears that I can put the value 4 in a cell and reference it, but can I just use a variable which is =4? If so, what is the appropriate syntax? e.g., CriteriaRange:= (Sheet1.range(K:K).value = 4) (2) Ultimately, what I really want is a count of the unique values that meet my criteria, so can I use a Count against the returned range, without sending it to a physical page location? Perhaps I can make a named range that references an array instead of a range, and use that to generate a count in a second step? Initially I was using formulas to get my unique counts, but using a sumproduct formula (actually, several dozen of them) against an array this large was just taking too long, and initial tests with AdvancedFilter indicated it would be much faster. I appreciate any syntax hints with the AdvancedFilter, or even confirmation on whether or not what I'm attempting is even possible with AdvancedFilter. My backup option is to just loop the page and sort the data into a 2D array (while keeping track of whether each value is unique), but AdvancedFilter seemed like a more direct option (no loops, etc) Thank you, Keith |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
AdvancedFilter syntax
I think you'll have to use a real range for that criteria.
And I'm not sure that this is quicker than reading the range into an array and looping through that -- but it was pretty darn fast even with 10000 rows. Option Explicit Sub testme() Dim TempWks As Worksheet Dim CritWks As Worksheet Dim wks As Worksheet Dim myRng As Range Dim LastRow As Long Dim LastCol As Long Dim HowMany As Long Application.ScreenUpdating = False Set wks = Worksheets("Sheet1") Set CritWks = Worksheets.Add Set TempWks = Worksheets.Add With wks LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row LastCol = .Cells(1, .Columns.Count).End(xlToLeft).Column Set myRng = .Range("A1", .Cells(LastRow, LastCol)) 'copy the headers .Rows(1).Copy _ Destination:=CritWks.Range("a1") End With 'put the value you want in K2 of the criteria worksheet CritWks.Range("K2").Value = 4 'copy the 4's to the new sheet myRng.AdvancedFilter _ Action:=xlFilterCopy, _ CriteriaRange:=CritWks.UsedRange, _ CopyToRange:=TempWks.Range("A1"), _ Unique:=False 'just uniques in column A With TempWks .Range("A:A").AdvancedFilter _ Action:=xlFilterInPlace, _ CriteriaRange:="", _ CopyToRange:="", _ Unique:=True End With With TempWks 'avoid the header in row 1 HowMany = .Range("A1", .Cells(.Rows.Count, "A").End(xlUp)) _ .Cells.SpecialCells(xlCellTypeVisible).Cells.Count - 1 End With With Application .DisplayAlerts = False CritWks.Delete TempWks.Delete .DisplayAlerts = True .ScreenUpdating = True End With MsgBox HowMany End Sub ker_01 wrote: Currently, I am using: Sheet3.Range("A1:A10000").AdvancedFilter _ Action:=xlFilterCopy, _ CopyToRange:=Sheet2.Range("A1"), _ Unique:=True This generates a list of the unique values from my source list. While I can get to my real end result through several additional steps, I suspect that I'm underutilizing AdvancedFilter, and am hoping for a few pointers on how to get it to return some more specific information. Specifically, (1) I do have a criteria that I'd like to use without placing it in a cell range (Select unique values from A1:A10000 where the corresponding value in Column K is '4'). It appears that I can put the value 4 in a cell and reference it, but can I just use a variable which is =4? If so, what is the appropriate syntax? e.g., CriteriaRange:= (Sheet1.range(K:K).value = 4) (2) Ultimately, what I really want is a count of the unique values that meet my criteria, so can I use a Count against the returned range, without sending it to a physical page location? Perhaps I can make a named range that references an array instead of a range, and use that to generate a count in a second step? Initially I was using formulas to get my unique counts, but using a sumproduct formula (actually, several dozen of them) against an array this large was just taking too long, and initial tests with AdvancedFilter indicated it would be much faster. I appreciate any syntax hints with the AdvancedFilter, or even confirmation on whether or not what I'm attempting is even possible with AdvancedFilter. My backup option is to just loop the page and sort the data into a 2D array (while keeping track of whether each value is unique), but AdvancedFilter seemed like a more direct option (no loops, etc) Thank you, Keith -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Question about AdvancedFilter | Excel Programming | |||
AdvancedFilter | Excel Programming | |||
Advancedfilter | Excel Programming | |||
AdvancedFilter with VBA | Excel Programming | |||
AdvancedFilter in VB | Excel Programming |