Storing unique values in array

[cwmancuso]

I am processing an Excel spreadsheet, attempting to store the unique values from one of the columns (location) to an array. If the location already exists - skip it, otherwise store the value in the array. The code seems to work properly, storing/skipping the locations, but then stops recognizing that the 1st value in the array already exists.

Through some debugging, I have found that when I call the function to check the array, it starts at element zero, and doesn't recognize that a value being added already exists in the array.

I have attached a Notepad file with the code and some data. The code expects the data to be an Excel table called, "tblNexpose" on a worksheet called "Sheet1"

[Paul_Hossler]

1. Welcome to VBAexpress

2. Attaching a workbook with the macros and data saves people the problem of guessing and recreating what they think your data might look like

3. I thought your approach was a little complicated just to create a unique items array. Some people prefer Advanced Filter, but I usually use a Collection



Option Explicit

Private Sub cmdSummary_Click()
Dim A() As String
Dim C As Collection
Dim R As Range
Dim I As Long

Set C = New Collection

On Error Resume Next
For Each R In Worksheets("Sheet1").Range("D1:D10").Cells
C.Add R.Value, CStr(R.Value)
Next
On Error GoTo 0

ReDim A(1 To C.Count)

For I = 1 To C.Count
A(I) = C.Item(I)
Next I


For I = LBound(A) To UBound(A)
MsgBox A(I)
Next I

MsgBox "Done" 'Pause during testing to check values
End Sub

[cwmancuso]

Thank you for the quick response, Paul! I'm relatively new to VBA, and although the bigger picture for this code is more than what I portrayed, you are probably correct about my over-complicating the problem. Thanks again!

[Paul_Hossler]

You could probably modularize the Unique List code into a more general purpose function




Option Explicit

Sub drv()
Dim v As Variant
Dim v1 As Variant

v = UniqueList(Worksheets("Sheet1").Range("D1:D10"))

For Each v1 In v
MsgBox v1
Next
End Sub


Public Function UniqueList(R As Range) As Variant
Dim A() As String
Dim C As Collection
Dim R1 As Range
Dim I As Long

Set C = New Collection

On Error Resume Next
For Each R1 In R.Cells
C.Add R1.Value, CStr(R1.Value)
Next
On Error GoTo 0

ReDim A(1 To C.Count)

For I = 1 To C.Count
A(I) = C.Item(I)
Next I

UniqueList = A
End Function





You can mark your thread / question [Solved] by using [Tread Tools] menu above your first post

[snb]

You'd better use a dictionary


sub M_snb()
sn=sheets(1).range("D1:D10")

with createobject("scripting.dictionary")
for each it in sn
x0=.item(it)
next

msgbox join(.keys,vblf)
end with
End sub