Re: Unique Value by Advance Filter

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

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.

beersa.beersa@xxxxxxxxx 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

.

Quantcast