Adding records to an array in Excel VBA

I have an excel vba program where I need to cycle through a column of invoice numbers and determine if the number has already appeared in the column. So I need to check an array or collection of numbers.  If the number is there, I need to delete the row and move on to the next row.  If the number is not in the array, I need to add it to the array and then move on to the next row. I know how to cycle through the rows, but I don't know how to add a record to an array nor how to check to see if a record already exists in an array.

T
T HoecherlDeveloperAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
How do you populate the array which you are comparing the invoice numbers with?
Can you upload a small sample workbook (max 20-25 rows) and mock up the desired output manually on another sheet to show what you are trying to achieve?
0
T HoecherlDeveloperAuthor Commented:
That's the problem.  I don't know how to populate the array nor how to compare the numbers.
0
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
I have an excel vba program where I need to cycle through a column of invoice numbers and determine if the number has already appeared in the column. So I need to check an array or collection of numbers.  If the number is there, I need to delete the row and move on to the next row.

Does that mean you want to delete the duplicate invoice numbers from a column?

Why not upload a sample workbook as requested and show us the end result you are trying to achieve?
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

Martin LissOlder than dirtCommented:
To get rid of duplicates, select at least one cell in the invoices column, and then on the 'Data' tab select 'Remove Duplicates'. If you record a macro while doing that (including selecting the cell) you should be able to reuse it when you want to.
0
T HoecherlDeveloperAuthor Commented:
The remove duplicates solution is very promising. Here is a portion of the code I am using:

    x = ActiveCell.Address
    ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Activate
    y = ActiveCell.Address
    ActiveSheet.Range("x:y").RemoveDuplicates Columns:=1, Header:=xlNo

x captures the address of A8.  Y captures the address of E13.  But when I put it in the code as you see above, nothing happens.  If I put it in as x:y, it throws a syntax error.  So, too with "x":"y".  If I put in the actual cell adresses, ("A8:E13"), it works great, but I can't do that, I have to be able to use the variables.  Is there a way I can do this using the variables?
0
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
Like this...

ActiveSheet.Range(Range(x), Range(y)).RemoveDuplicates Columns:=1, Header:=xlNo

Open in new window

0
Martin LissOlder than dirtCommented:
When you put something in quotes like "x:y", VBA will think that that is a string and so the values of x and y will not be substituted. To do want you want, do this:


.Range(x &":" & y)
0
T HoecherlDeveloperAuthor Commented:
Brilliant again Neeraj.  Thanks.
0
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
You're welcome T!
What Martin suggested would also work for you.

Also, you should avoid Select and Activate unless really required like in case of ActiveCell.
0
T HoecherlDeveloperAuthor Commented:
Martin Liss,

You were the one who suggested the elimination of the duplicates.  I should have awarded the points to you.  That was an oversight on my part. I know Neeraj, so I clicked on his name, but that was unfair. I apologize.
0
Martin LissOlder than dirtCommented:
T Hoecherl, you can now re-assign points in this question.
1

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
VBA

From novice to tech pro — start learning today.