Thread: Array Help / Understanding

Hello,

Im hoping someone can help me.

I have inherited the following code and although I understand exactly what it is doing, i am still very much in the learning stage when it comes to arrays.

Option Explicit
Option Base 1
Public strValues() As String

Sub AddUniqueValues()

    Dim strValues() As String
    Dim intNewValue As Integer
    Dim strCurrentValue As String
    Dim intTargetRow As Integer
    Dim blnValueExists As Boolean
    Dim i As Integer
    Dim j As Integer
    
    intNewValue = 1
    
    ReDim strValues(1)
    
    strValues(1) = Sheets(1).Range("E2").Value
    
    For i = 2 To Sheets(1).Range("A1").CurrentRegion.Rows.Count
    
        strCurrentValue = Sheets(1).Cells(i, 5).Value
        
        For j = 1 To UBound(strValues)
               
               blnValueExists = False
            
            If strCurrentValue = strValues(j) Then
            
                blnValueExists = True
                Exit For
                
             End If
    
        Next j
        
            If blnValueExists = False Then
            
                intNewValue = intNewValue + 1
            
                ReDim Preserve strValues(intNewValue)
                
                strValues(intNewValue) = strCurrentValue
    
            End If
    
    Next i
        
    intTargetRow = 1
        
    For i = 1 To UBound(strValues)
    
        Sheets(2).Cells(intTargetRow, 1) = strValues(i)
            
        intTargetRow = intTargetRow + 1
    Next i
    
End Sub

What im wanting to do is capture the row number of the unique value and add this into the array in order to then reference it back out to Sheets(2).Cells(intTargetRow, 2)

Can anyone help?

Any text to better help my understanding of how arrays can be manipulated and built up would be greatly appreciated.

Thanks

Hello McC,

Here is another way to find the uniques. This is faster and more flexible than your previous macro. The Dictionary object is an associative array, also known as a hash table in other languages. This allows you to randomly access array elements, check if they exist, add to them, and remove them. All the data is stored in pair values. A pair value consists of a "key" which is a unique element ID and an "item". The item can be any object or data type except for a User Defined Type.

Sub AddUniqueValues1()

  Dim Cell As Range
  Dim Dict As Object
  Dim DstRng As Range
  Dim DstWks As Worksheet
  Dim Key As Variant
  Dim R As Long
  Dim Rng As Range
  Dim SrcWks As Worksheet
  
    Set SrcWks = Worksheets("sheet1")
    Set DstWks = Worksheets("Sheet2")
      
    Set DstRng = DstWks.Range("A2")
      
    Set Rng = SrcWks.Range("A1").CurrentRegion
    Set Rng = Intersect(Rng, Rng.Offset(1, 0))
      
      Set Dict = CreateObject("Scripting.Dictionary")
      'Uncomment the line below to ignore case for the Keys
      'Dict.CompareMode = vbTextCompare
      
      For Each Cell In Rng.Columns(5).Cells
        Key = Trim(Cell)
        If Key <> "" Then
          If Not Dict.Exists(Key) Then
             Dict.Add Key, Cell
          End If
        End If
      Next Cell
      
      For Each Key In Dict.Keys
        DstRng.Offset(R, 0) = Dict(Key).Value
        R = R + 1
      Next Key

End Sub

If I'm not mistaken a oneliner suffices:

Sub snb()
  sheets(1).columns(5).advancedfilter xlfiltercopy ,,sheets(1).cells(1),true
End Sub