Storing unique values in array

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"

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

Code:

---

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

---

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!

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

Code:

---

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

You'd better use a dictionary

Code:

---

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

---