Filter Column for Unique Values in VBA 1

[Crystalyzer]

I want to filter a column of data for unique values and place those values in an array variable for use in my VBA coding. I've recorded the steps to filter a list and copy unique values to another destination, however, I want to avoid copying the list of unique values to a range and go right to the variable. Is this possible?

Thanks for any help in advance.


Thanks and best regards,
-Lloyd

 

[SkipVought]

Hi,

dim r as range, a(), i as integer
i=0
for each r in range(cells(2,"B"), cells(2,"B").end(xldown)).specialcells(xlcelltypevisible)
  redim preserve a(i)
  a(i) = r.value
  i=i+1
next

Skip,

_{[red]Be advised:[/red] When you ignite a firecracker in a bowl of vanilla, chocolate & strawberry ice cream, you get...
Neopolitan Blownapart!}

 

[Crystalyzer]

Skip,

Thanks for the speedy reply! It would have worked if my question was phrased better. My column is NOT filtered already and contains many duplicate entries. I am trying to find a way to determine the unique values and then store them in an array variable. I believe what you have given me is a way to store the values of an already filtered list unless I am missing something which is ENTIRELY possible.

My list is as follows:

column A
Locations
East
East
West
South
West
North
North East
North East
South West
East

I want the array varialbe to only list the unique items without my having to filter the data first. Is this possible?





Thanks and best regards,
-Lloyd

 

[SkipVought]

1. Use Data/Filter/advanced Filter to generate a UNIQUE list

2. Loop thru that list to capture array values.

of just loop thru the original list and test to see if the current value has already been captured.

assuming that the list is sorted...

dim r as range, a(), i as integer
i=0
for each r in range(cells(2,"B"), cells(2,"B").end(xldown))
  with r
    if .value <> a(i) then
      redim preserve a(i)
      a(i) = r.value
      i=i+1
    end if  
  end with   
next

Skip,

_{[red]Be advised:[/red]When Viscounts were guillotined just as they were disclosing where their jewels were hidden, it shows to go that you should...
Never hatchet your Counts before they chicken!}

 

[Crystalyzer]

Skip,

I'm complicating things too much. A brick hit me in the head that I could filter the list in place for unique values, run your code to populate the array variable and then show all data to return to the original.

Thanks for the push in the right direction

Thanks and best regards,
-Lloyd

 

[SkipVought]

sorry

dim r as range, a(), i as integer
i=0
for each r in range(cells(2,"B"), cells(2,"B").end(xldown))
  with r
    if i > 0 then    
      if .value <> a(i) then
        i=i+1
        redim preserve a(i)
        a(i) = .value
      end if  
    else
      redim preserve a(i)
      a(i) = .value
    end if
  end with   
next

Skip,

_{[red]Be advised:[/red]When Viscounts were guillotined just as they were disclosing where their jewels were hidden, it shows to go that you should...
Never hatchet your Counts before they chicken!}