| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Unique Value by Advance Filter
Can I store the result of unique value by using advanced filter in VBA?
Sheets(mysheet).Range(myR).AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Sheets(mysheet).Range( _ myR), CopyToRange:=Sheets(mysheet).Range("S5"), Unique:=True Instead of CopytoRange:=<, how can store in to variable? Thanks Rgds BeerSA |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Unique Value by Advance Filter
I think you'll need to use a range. But you could pick up that range and put it
into an array. wrote: Can I store the result of unique value by using advanced filter in VBA? Sheets(mysheet).Range(myR).AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Sheets(mysheet).Range( _ myR), CopyToRange:=Sheets(mysheet).Range("S5"), Unique:=True Instead of CopytoRange:=<, how can store in to variable? Thanks Rgds BeerSA -- Dave Peterson |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Unique Value by Advance Filter
Is there anyway to replace the "S5" with a variable?
CopyToRange:=Sheets(mysheet).Range("S5") BrianR "Dave Peterson" wrote: I think you'll need to use a range. But you could pick up that range and put it into an array. wrote: Can I store the result of unique value by using advanced filter in VBA? Sheets(mysheet).Range(myR).AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Sheets(mysheet).Range( _ myR), CopyToRange:=Sheets(mysheet).Range("S5"), Unique:=True Instead of CopytoRange:=<, how can store in to variable? Thanks Rgds BeerSA -- Dave Peterson |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Unique Value by Advance Filter
dim DestCell as range
..... set destcell = sheets(mysheet).range("s5") ..... ...copytorange:=destcell BrianR wrote: Is there anyway to replace the "S5" with a variable? CopyToRange:=Sheets(mysheet).Range("S5") BrianR "Dave Peterson" wrote: I think you'll need to use a range. But you could pick up that range and put it into an array. wrote: Can I store the result of unique value by using advanced filter in VBA? Sheets(mysheet).Range(myR).AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Sheets(mysheet).Range( _ myR), CopyToRange:=Sheets(mysheet).Range("S5"), Unique:=True Instead of CopytoRange:=<, how can store in to variable? Thanks Rgds BeerSA -- Dave Peterson -- Dave Peterson |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Unique Value by Advance Filter
Is there anyway to replace the "S5" with a variable?
CopyToRange:=Sheets(mysheet).Range("S5") BrianR "Dave Peterson" wrote: dim DestCell as range ..... set destcell = sheets(mysheet).range("s5") ..... ...copytorange:=destcell BrianR wrote: Is there anyway to replace the "S5" with a variable? CopyToRange:=Sheets(mysheet).Range("S5") BrianR "Dave Peterson" wrote: I think you'll need to use a range. But you could pick up that range and put it into an array. wrote: Can I store the result of unique value by using advanced filter in VBA? Sheets(mysheet).Range(myR).AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Sheets(mysheet).Range( _ myR), CopyToRange:=Sheets(mysheet).Range("S5"), Unique:=True Instead of CopytoRange:=<, how can store in to variable? Thanks Rgds BeerSA -- Dave Peterson -- Dave Peterson |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Unique Value by Advance Filter
Dim myAddr as String
myaddr = "s5" ....CopyToRange:=Sheets(mysheet).Range(myAddr) BrianR wrote: Is there anyway to replace the "S5" with a variable? CopyToRange:=Sheets(mysheet).Range("S5") BrianR "Dave Peterson" wrote: dim DestCell as range ..... set destcell = sheets(mysheet).range("s5") ..... ...copytorange:=destcell BrianR wrote: Is there anyway to replace the "S5" with a variable? CopyToRange:=Sheets(mysheet).Range("S5") BrianR "Dave Peterson" wrote: I think you'll need to use a range. But you could pick up that range and put it into an array. wrote: Can I store the result of unique value by using advanced filter in VBA? Sheets(mysheet).Range(myR).AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Sheets(mysheet).Range( _ myR), CopyToRange:=Sheets(mysheet).Range("S5"), Unique:=True Instead of CopytoRange:=<, how can store in to variable? Thanks Rgds BeerSA -- Dave Peterson -- Dave Peterson -- Dave Peterson |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Unique Value by Advance Filter
That's getting me closer. It doesn't double my first ID but it does use the
header as a value to find as a unique ID and puts it in my list and counts it. All I did was change the "A2" to "A1" on line 7. ID List Qty <--- header (manually put in at "O1 and P1" ID 1 <-- ID gets picked up in my list of IDs to filter 500 1008 502 504 503 516 504 504 505 926 Public Sub ExtractUniqueAndSort(Sheet As String, last_row_used_local As Integer) Dim destcell As String destrow = 2 destcell = Sheets(Sheet).Cells(destrow, ID_List).Address With Sheets(Sheet) 'extract unique IDs from column A Range("A1:A" & last_row_used_local).AdvancedFilter Action:=xlFilterCopy, _ CopyToRange:=Sheets(Sheet).Cells(destrow, ID_List), Unique:=True 'sort the unique IDs .Range(.Range(destcell), .Range(destcell).End(xlDown)) _ .Sort Key1:=.Range(destcell), Order1:=xlAscending, Header:=xlYes End With End Sub " wrote: Can I store the result of unique value by using advanced filter in VBA? Sheets(mysheet).Range(myR).AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Sheets(mysheet).Range( _ myR), CopyToRange:=Sheets(mysheet).Range("S5"), Unique:=True Instead of CopytoRange:=<, how can store in to variable? Thanks Rgds BeerSA |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Unique Value by Advance Filter
When you're looping through that unique list, make sure you put the manual
headers in a different column. Then start with row 2 in your loop. BrianR wrote: That's getting me closer. It doesn't double my first ID but it does use the header as a value to find as a unique ID and puts it in my list and counts it. All I did was change the "A2" to "A1" on line 7. ID List Qty <--- header (manually put in at "O1 and P1" ID 1 <-- ID gets picked up in my list of IDs to filter 500 1008 502 504 503 516 504 504 505 926 Public Sub ExtractUniqueAndSort(Sheet As String, last_row_used_local As Integer) Dim destcell As String destrow = 2 destcell = Sheets(Sheet).Cells(destrow, ID_List).Address With Sheets(Sheet) 'extract unique IDs from column A Range("A1:A" & last_row_used_local).AdvancedFilter Action:=xlFilterCopy, _ CopyToRange:=Sheets(Sheet).Cells(destrow, ID_List), Unique:=True 'sort the unique IDs .Range(.Range(destcell), .Range(destcell).End(xlDown)) _ .Sort Key1:=.Range(destcell), Order1:=xlAscending, Header:=xlYes End With End Sub " wrote: Can I store the result of unique value by using advanced filter in VBA? Sheets(mysheet).Range(myR).AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Sheets(mysheet).Range( _ myR), CopyToRange:=Sheets(mysheet).Range("S5"), Unique:=True Instead of CopytoRange:=<, how can store in to variable? Thanks Rgds BeerSA -- Dave Peterson |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Unique Value by Advance Filter
Sorry to keep bugging you about this Dave...
I'm a little confused . Here's the top portion of the spreadsheet (C - N are hidden for space reasons). A B O P ID Date "IDList" "Qty of IDs" ---- ----------- -------- ------------- 500 6/29/2005 500 1008 500 6/29/2005 502 504 500 6/29/2005 504 504 502 6/29/2005 502 6/29/2005 502 6/29/2005 "Dave Peterson" wrote: When you're looping through that unique list, make sure you put the manual headers in a different column. Then start with row 2 in your loop. BrianR wrote: That's getting me closer. It doesn't double my first ID but it does use the header as a value to find as a unique ID and puts it in my list and counts it. All I did was change the "A2" to "A1" on line 7. ID List Qty <--- header (manually put in at "O1 and P1" ID 1 <-- ID gets picked up in my list of IDs to filter 500 1008 502 504 503 516 504 504 505 926 Public Sub ExtractUniqueAndSort(Sheet As String, last_row_used_local As Integer) Dim destcell As String destrow = 2 destcell = Sheets(Sheet).Cells(destrow, ID_List).Address With Sheets(Sheet) 'extract unique IDs from column A Range("A1:A" & last_row_used_local).AdvancedFilter Action:=xlFilterCopy, _ CopyToRange:=Sheets(Sheet).Cells(destrow, ID_List), Unique:=True 'sort the unique IDs .Range(.Range(destcell), .Range(destcell).End(xlDown)) _ .Sort Key1:=.Range(destcell), Order1:=xlAscending, Header:=xlYes End With End Sub " wrote: Can I store the result of unique value by using advanced filter in VBA? Sheets(mysheet).Range(myR).AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Sheets(mysheet).Range( _ myR), CopyToRange:=Sheets(mysheet).Range("S5"), Unique:=True Instead of CopytoRange:=<, how can store in to variable? Thanks Rgds BeerSA -- Dave Peterson |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| advance filter | Excel Discussion (Misc queries) | |||
| Excel 2002: Advance filter -How to select table with unique refere | Excel Discussion (Misc queries) | |||
| Advance Filter + Unique Records | Excel Discussion (Misc queries) | |||
| how do I advance filter a large csv file for unique customer #'s | Excel Worksheet Functions | |||
| Advance filter search does not filter an exact match | Excel Programming | |||
Hybrid Mode
