Array Validation | [A-Z] Sort + Remove Duplicates

[SoniboiTM]

Problem: Array Manipulation

1. Requirements:

- B4: Keep as it is.

- Data source: from B4 up to first non-empty cells in column B.

- Remove non-security code data (enclosed in parenthesis, e.g., (deposit), (subscribe), (etc.))

- Remove the duplicate

- Array data - sort in ascending order.

- Print output data in D4, D5, etc. depends on how many items are there in array.

2. VBA Coding

Sub RefreshTSData()
    Dim vMyArray() As Variant
    Dim vNames As Range
    Dim vCell As Variant
    Dim iCtr As Long
   
    Set vNames = Sheets("J2-Trading Journal").Range("A1:A7")   'A7 = last row of non-empty cells

    iCtr = 0
    For Each vCell In vNames
        If Mid(vCell.Value, 1, 1) = "(" Then
            'do not add to array
        Else
            'validate: if duplicate value, do not add to array
            'validate: if unique value, add to array
            iCtr = iCtr + 1
            vMyArray(iCtr) = vCell     'error: out of range
       End If

       'Debug.Print vMyArray(iCtr)

    Next vCell
   
    Range("C1:C5000").Value = vMyArray.Value  'Error
End Sub

3. Need to learn more technique in data manipulations, too. This time, on how to use an array effectively.

Any idea, sirs?

Thanks in advance for your help.

 

[Peter_SSs]

Appreciated.

 

[SoniboiTM]

Here's how you provided help for me. I can concentrate now on my other stuff.
I still have two (2) more concerns but I will try to solve it first. Then? Hope it will be okay to ask your help again... : )

Now, am not worried about losing data.

Because it is the program itself that will, ...

- format existing worksheets automatically
- validate the data
- update data every time I click on each worksheet
- apply conditional formatting on each worksheet (changing colors, alignments, table borders, etc)
- apply proper alignment and computations
- prepare report
- verify if the necessary worksheet(s) is/are available (I only need one general worksheet to be updated and the program will do the rest [note: still working in some areas])
- merge and unmerge rows and columns
- create named range (Name Manager)
- etc., etc., etc.

That's all spells accuracy.

 

[Fluff]

If you get stuck, just start a new thread.