Solved Extract Unique Values from Range Variable

Microsoft Excel 2010 - complete product...
July 24, 2018 at 02:13:30
Specs: Windows 7, 2,4 GHz / 4 GB
I've been busting my head for hours now and I can't seem to find a code to work without having to use an actual cell range in the sheet. I've tried arrays, collections, but I can't store the values of the resulting array/collection of unique values to a range variable.

myRange in the below code is selected elsewhere via input box and is, for example:
009001001
009001001
009001001
009001002

Dim myArray As Variant
Dim counter As Long
Dim myDict As Object
Dim newRange As Range

myArray = myRange.Value
Set myDict = CreateObject("Scripting.Dictionary")
    myDict.CompareMode = vbTextCompare
For counter = 1 To UBound(myArray, 1)
    myDict.Item(myArray(counter, 1)) = myArray(counter, 1)
Next

myArray = myDict.Items  'SO FAR SO GOOD, here myArray contains 2 unique items: 
'009001001 and 009001002

newRange = myArray '<-------- it's here where I'm having the problem 
'as I can't find the right syntax to put this to a new range so I can loop (below)

Dim x As Object
For Each x In newRange
    tmpBody = tmpBody & "- invoice no. " & x & vbNewLine & vbNewLine
Next x

I know I can write the transposed array to the sheet in a range of cells, but I wanted to just store the values in a newRange so I can loop through it and add those unique values to my email's body.

If you have any suggestions to make this work without having to go through writing values to cells, please share.

Thank you in advance.


See More: Extract Unique Values from Range Variable

Reply ↓  Report •

#1
July 24, 2018 at 03:47:32
I'm a bit confused, but it may be just because of your choice of variable names.

You say that you don't want to write the array to a cell range, yet you dimensioned newRange as Range. Why not just loop through myArray since that is now what holds your unique values?

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code

message edited by DerbyDad03


Reply ↓  Report •

#2
July 24, 2018 at 03:53:28
Even if it would stay as an array, how could I loop the array to paste its values to the email body?

For Each x In myArray
    tmpBody = tmpBody & "- invoice no. " & x & vbNewLine & vbNewLine
Next x

Because the whole point of putting the new myArray into a range was so I can loop through it and "print" its values into my email's body. But I would gladly give up on that, if it works in any other way except having to copy array values to cells.

message edited by Mrrrr


Reply ↓  Report •

#3
July 24, 2018 at 06:20:40
✔ Best Answer
re: "Even if it would stay as an array, how could I loop the array to paste its values to the email body?"

You appear to know how to loop through an array since you did it here:

For counter = 1 To UBound(myArray, 1)
    myDict.Item(myArray(counter, 1)) = myArray(counter, 1)
Next

Just loop through the updated array in the same manner:

'newRange = myArray (not needed)
'Dim x As Object (not needed)

  For nxtItem = 0 To UBound(myArray)
    tmpBody = tmpBody & "- invoice no. " & myArray(nxtItem) & vbNewLine & vbNewLine
    MsgBox tmpBody 'Used for testing
  Next

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code


Reply ↓  Report •

Related Solutions

#4
July 24, 2018 at 06:44:56
Yes, well, that was looping through an array and inserting data into another array, while the 2nd time I was trying to insert data into plain text from an array.

I was going to say it's not working, then I remembered that I forgot to copy my code back (had it removed to test some other options I want to add). Still didn't work and when stepping through it I realized I had changed a range name when posting here. :)

It's working and now you taught me how to paste values from an array to text. Thank you very much!

I hope I manage doing by myself the other option I'm trying to add to the code, which is checking if email was sent then adding send date into cell and confirmation (email sent) in another cell. If not, I might be starting a new question tomorrow... I was hoping I'd manage making feature-rich code by myself now, but there are still many, many things to learn...


Reply ↓  Report •

#5
July 24, 2018 at 07:42:28
re: "Yes, well, that was looping through an array and inserting data into another array, while the 2nd time I was trying to insert data into plain text from an array."

Well, actually, you weren't "trying to insert data into plain text from an array". You may have wanted to, but you weren't actually using an array. ;-)

IOW, this code does not reference an array:

Dim x As Object
For Each x In newRange
    tmpBody = tmpBody & "- invoice no. " & x & vbNewLine & vbNewLine
Next x

There isn't much difference between loading an array with elements from an array vs. building a text string with those same elements. In both cases you have to refer to the elements within the original array.

The following snippet does both using the same loop.

ReDim aDifferentArray(UBound(myArray))

'Build Text String and Populate "A Different Array" Using The Same Method
  For nxtItem = 0 To UBound(myArray)
    tmpBody = tmpBody & "- invoice no. " & myArray(nxtItem) & vbNewLine & vbNewLine
    aDifferentArray(nxtItem) = myArray(nxtItem)
  Next
  
'Display Complete Text String
    MsgBox "tmpBody Test" & vbNewLine & vbNewLine & tmpBody 
    
'Display Elements within "A Different Array"
  For nxtItem = 0 To UBound(aDifferentArray)
    MsgBox "Array Test Item # " & nxtItem & vbNewLine & vbNewLine & _
           aDifferentArray(nxtItem)
  Next

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code


Reply ↓  Report •

#6
July 25, 2018 at 01:16:17
re: "Well, actually, you weren't "trying to insert data into plain text from an array". You may have wanted to, but you weren't actually using an array. ;-)"

Yes, because I didn't know the syntax to send array elements to text, and I thought I'd first send the array to a range and loop range and send its elements to text. Confusing approach, I know, but I wanted something to work and manage making the code by myself. Until I couldn't. :)
I actually "prepared" a code to skip array and dictionary usage and copy all values from the initial range to a new cell range in column T (out of the way), then use Range.RemoveDuplicates and copy the remainin unique values to the email body.

Thanks also for the snippet.
I think I will remain with the initial code (in the Best Answer post). Second one looks a bit more complicated :) even if it's not.


Reply ↓  Report •

Ask Question