Unique Value by Advance Filter

[beersa.beersa]

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]

I think you'll need to use a range. But you could pick up that range and put it
into an array.

 

[BrianR]

Is there anyway to replace the "S5" with a variable?

CopyToRange:=Sheets(mysheet).Range("S5")

BrianR

 

[Dave Peterson]

dim DestCell as range
.....
set destcell = sheets(mysheet).range("s5")
.....
...copytorange:=destcell

 

[BrianR]

Is there anyway to replace the "S5" with a variable?

CopyToRange:=Sheets(mysheet).Range("S5")

BrianR

 

[Dave Peterson]

Dim myAddr as String
myaddr = "s5"

....CopyToRange:=Sheets(mysheet).Range(myAddr)

Is there anyway to replace the "S5" with a variable?

CopyToRange:=Sheets(mysheet).Range("S5")

BrianR

 

[BrianR]

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]

....copytorange:=sheets(mysheet).cells(5,id_list)

I used 5 as the row. I have no idea where that's coming from.

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).

 

[BrianR]

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]

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()"

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

 

[BrianR]

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

 

[BrianR]

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

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]

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]

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

 

[Dave Peterson]

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]

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