Excel VBA Need Help With Unique Values and Building a String 1

[JasonEnsor]

Hi Guys,

Appologies if the title doesn't make much sense, but i didn't quite know what to call this.

Background Info:
My department runs a report from PeopleSoft that saves as a PDF, The file is split up in various ways and some Adobe Javascripts are run on the PDFs. Accross these PDF's there are certain keywords that are searched for and the pages that include those keywords are extracted in to one folder then are rebuilt in to a PDF.

Current Process:
Currently this is done manually...it's a major ball ache as currently the individual doing the taks searches for the keyword in the pdf, then makes a list of what pages the keyword appears on then extracts each page one at a time.

Updated Process:
What i found was in Adobe Pro X, i was able to do an advanced search for the keyword, which then lists all of the pages the keyword appears upon and can save the data in to a csv file, that i can manipulate in excel. I have also created a javascript that i can run in Adobe to extract all pages in an array.

Help Needed:
I have created several small macros that i have bolted together to clearout all the unneeded data from the csv file and to create a text string in a cell that i can then past in to the adobe script...it's still a manual job, but better than before.

The issue i am facing is i am wanting to get all unique values from Column A (as keywords appear twice on the same page), and delete the duplicates. This currently Keeps the first value twice, so 23,23,40,14,53,53,10 would end up being 23,23,40,14,53,10 . I can't figure out how to stop it duplicating that first value

Public Sub GetUnique()
      currentLastRow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
      ActiveSheet.Range("A:A").AdvancedFilter Action:=xlFilterCopy, _
      CopyToRange:=ActiveSheet.Range("B1"), Unique:=True
      ActiveWorkbook.ActiveSheet.Range("A:A").EntireColumn.Delete
      
      Call BuildArray
End Sub

Once those value are created and all data extra data is removed i am wanting to format this as a string in the form of an array so [23,40,14,53,10]. However i can only seem to get it to do the following [23,23,40,14,53,10,].
If i can sort out the duplicate value then that will make it easier, what i am needing then is an idea of how best to stop the extra , at the end of the array string.

My code is

Sub BuildArray()

Dim ArrayVar


    currentLastRow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
    
For Each Item In ActiveSheet.Range("A1:A" & currentLastRow)
    Item.Rows.EntireRow.Select
    ArrayVar = ArrayVar + CStr((ActiveCell.Value - 1)) + ","
    
Next
ActiveSheet.Range("B1").Value = "[" + ArrayVar + "]"


End Sub

It's possibly something daft i am missing but i can't see it, it's been a full on week of stress so i thought a second set of eyes might help.

Any thoughts or help would be appreciated.

J.

 

[PHV]

Replace this:
ArrayVar = ArrayVar + CStr((ActiveCell.Value - 1)) + ","
with this:
ArrayVar = ArrayVar & "," & CStr(ActiveCell.Value - 1)

And this:
ActiveSheet.Range("B1").Value = "[" + ArrayVar + "]"
with this:
ActiveSheet.Range("B1").Value = "[" & Mid(ArrayVar, 2) & "]"

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[JasonEnsor]

Hi PHV,

Utilising your suggestion and setting my data for the array to start on the 2nd row (this removes the duplicate from my array range) i am able now to populate my data in the format needed.

Many Thanks

Jason.