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.

"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.

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?

T HoecherlDeveloperAuthor Commented: 2018-05-29

That's the problem. I don't know how to populate the array nor how to compare the numbers.

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?

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

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.

T HoecherlDeveloperAuthor Commented: 2018-05-29

The remove duplicates solution is very promising. Here is a portion of the code I am using:

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?

Also, you should avoid Select and Activate unless really required like in case of ActiveCell.

T HoecherlDeveloperAuthor Commented: 2018-05-30

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.