|
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 |
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 |
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 |
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 |
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 |
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 |
Unique Value by Advance Filter
If my column variable is coming in as an integer (ID_List = 15). My help
isn't working on Excel/VBA for getting info on Str$ so I'm trying this route (web). "Dave Peterson" wrote: 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 |
Unique Value by Advance Filter
....copytorange:=sheets(mysheet).cells(5,id_list)
I used 5 as the row. I have no idea where that's coming from. BrianR wrote: If my column variable is coming in as an integer (ID_List = 15). My help isn't working on Excel/VBA for getting info on Str$ so I'm trying this route (web). "Dave Peterson" wrote: 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 -- Dave Peterson |
Unique Value by Advance Filter
Thanks Dave that worked!
One last issue, when I run this AutoFilter macro, it keeps putting the first unique 500 found (from column A) into my new ID List twice. It only does it for the first item. ID List 607 607 614 615 616 "Dave Peterson" wrote: ....copytorange:=sheets(mysheet).cells(5,id_list) I used 5 as the row. I have no idea where that's coming from. BrianR wrote: If my column variable is coming in as an integer (ID_List = 15). My help isn't working on Excel/VBA for getting info on Str$ so I'm trying this route (web). "Dave Peterson" wrote: 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 -- Dave Peterson |
Unique Value by Advance Filter
I'd say that there was a difference between those two values.
Do you get the data from a web page? If so, it might be one of those HTML non-breaking space characters. David McRitchie has a macro that cleans up this kind of stuff: http://www.mvps.org/dmcritchie/excel/join.htm#trimall (look for "Sub Trimall()" BrianR wrote: Thanks Dave that worked! One last issue, when I run this AutoFilter macro, it keeps putting the first unique 500 found (from column A) into my new ID List twice. It only does it for the first item. ID List 607 607 614 615 616 "Dave Peterson" wrote: ....copytorange:=sheets(mysheet).cells(5,id_list) I used 5 as the row. I have no idea where that's coming from. BrianR wrote: If my column variable is coming in as an integer (ID_List = 15). My help isn't working on Excel/VBA for getting info on Str$ so I'm trying this route (web). "Dave Peterson" wrote: 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 -- Dave Peterson -- Dave Peterson |
Unique Value by Advance Filter
Dave,
I thought about that but when I read in different excel files (which is were this data is coming from) It happens still only on the first ID found. In other words, when I real in all of the data files, it then only finds one 607 (and displays it only once) but displays the 500 ID twice (which again happens to be the first ID listed). ID List Qty 500 1008 500 1008 502 504 503 516 504 504 505 926 506 503 507 84 508 504 509 503 510 426 511 419 512 504 513 504 514 420 515 421 600 588 601 588 602 588 603 588 604 504 605 504 606 504 607 506 608 504 609 504 611 504 612 504 613 504 614 504 615 502 616 504 "Dave Peterson" wrote: I'd say that there was a difference between those two values. Do you get the data from a web page? If so, it might be one of those HTML non-breaking space characters. David McRitchie has a macro that cleans up this kind of stuff: http://www.mvps.org/dmcritchie/excel/join.htm#trimall (look for "Sub Trimall()" BrianR wrote: Thanks Dave that worked! One last issue, when I run this AutoFilter macro, it keeps putting the first unique 500 found (from column A) into my new ID List twice. It only does it for the first item. ID List 607 607 614 615 616 "Dave Peterson" wrote: ....copytorange:=sheets(mysheet).cells(5,id_list) I used 5 as the row. I have no idea where that's coming from. BrianR wrote: If my column variable is coming in as an integer (ID_List = 15). My help isn't working on Excel/VBA for getting info on Str$ so I'm trying this route (web). "Dave Peterson" wrote: 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 -- Dave Peterson -- Dave Peterson |
Unique Value by Advance Filter
On another side note, it looks like the autofilter macro names the first cell
of my list as Extract. Why would it do this. I can remove the name (undefine it) and when I run the macro again, the name is back. BrianR "BrianR" wrote: Dave, I thought about that but when I read in different excel files (which is were this data is coming from) It happens still only on the first ID found. In other words, when I real in all of the data files, it then only finds one 607 (and displays it only once) but displays the 500 ID twice (which again happens to be the first ID listed). ID List Qty 500 1008 500 1008 502 504 503 516 504 504 505 926 506 503 507 84 508 504 509 503 510 426 511 419 512 504 513 504 514 420 515 421 600 588 601 588 602 588 603 588 604 504 605 504 606 504 607 506 608 504 609 504 611 504 612 504 613 504 614 504 615 502 616 504 "Dave Peterson" wrote: I'd say that there was a difference between those two values. Do you get the data from a web page? If so, it might be one of those HTML non-breaking space characters. David McRitchie has a macro that cleans up this kind of stuff: http://www.mvps.org/dmcritchie/excel/join.htm#trimall (look for "Sub Trimall()" BrianR wrote: Thanks Dave that worked! One last issue, when I run this AutoFilter macro, it keeps putting the first unique 500 found (from column A) into my new ID List twice. It only does it for the first item. ID List 607 607 614 615 616 "Dave Peterson" wrote: ....copytorange:=sheets(mysheet).cells(5,id_list) I used 5 as the row. I have no idea where that's coming from. BrianR wrote: If my column variable is coming in as an integer (ID_List = 15). My help isn't working on Excel/VBA for getting info on Str$ so I'm trying this route (web). "Dave Peterson" wrote: 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 -- Dave Peterson -- Dave Peterson |
Unique Value by Advance Filter
I didn't notice the "advance filter" stuff in the subject.
Data|Filter|Advanced filter uses the top cell in the column as a header. If you don't have real headers for your data, excel will use that first cell as a header--whether you want to or not. So if you don't have headers, add them. If you do have headers, make sure you include them in your code for the data|filter|advanced filter. BrianR wrote: On another side note, it looks like the autofilter macro names the first cell of my list as Extract. Why would it do this. I can remove the name (undefine it) and when I run the macro again, the name is back. BrianR "BrianR" wrote: Dave, I thought about that but when I read in different excel files (which is were this data is coming from) It happens still only on the first ID found. In other words, when I real in all of the data files, it then only finds one 607 (and displays it only once) but displays the 500 ID twice (which again happens to be the first ID listed). ID List Qty 500 1008 500 1008 502 504 503 516 504 504 505 926 506 503 507 84 508 504 509 503 510 426 511 419 512 504 513 504 514 420 515 421 600 588 601 588 602 588 603 588 604 504 605 504 606 504 607 506 608 504 609 504 611 504 612 504 613 504 614 504 615 502 616 504 "Dave Peterson" wrote: I'd say that there was a difference between those two values. Do you get the data from a web page? If so, it might be one of those HTML non-breaking space characters. David McRitchie has a macro that cleans up this kind of stuff: http://www.mvps.org/dmcritchie/excel/join.htm#trimall (look for "Sub Trimall()" BrianR wrote: Thanks Dave that worked! One last issue, when I run this AutoFilter macro, it keeps putting the first unique 500 found (from column A) into my new ID List twice. It only does it for the first item. ID List 607 607 614 615 616 "Dave Peterson" wrote: ....copytorange:=sheets(mysheet).cells(5,id_list) I used 5 as the row. I have no idea where that's coming from. BrianR wrote: If my column variable is coming in as an integer (ID_List = 15). My help isn't working on Excel/VBA for getting info on Str$ so I'm trying this route (web). "Dave Peterson" wrote: 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 -- Dave Peterson -- Dave Peterson -- Dave Peterson |
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 |
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 |
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 |
| All times are GMT +1. The time now is 10:46 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com