Find missing numbers in a range

[boboivan]

Hello everybody!

Currently I use the following VBA code to find missing numbers in a range ("C2:C3000"), worksheet "2014".

Please Login or Register  to view this content.

The missing numbers are listed in a new sheet called ("MissingNumbers"), in range ("B1:B").

Please, could somebody help me with improving this code in order to:
1. add exceptions that wouldn't be listed as missing numbers (e.g. 4360241, 4360256, etc).
2. count the missing numbers at the end of the range ("B1:B") (in the first empty row, of course).
3. copy the missing numbers (with the count number) in the range ("AJ2:AJ") of the worksheet ("2014").

Thank you very much in advance!

[Bernie Deitrick]

1) Place the list of exceptions into a specific range - say sheet "Exceptions" column A. Then change

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

2) Add this line before End Sub

Please Login or Register  to view this content.

3) And, add this line before End Sub

Please Login or Register  to view this content.

[boboivan]

Thank you so much for the prompt answer!

Everything works perfect. You are a genius!

[Bernie Deitrick]

You modified the code incorrectly: I have changed the whole macro to reflect the changes, and made a few other changes. Let me know...

Please Login or Register  to view this content.

[boboivan]

Yes sir, you're right!
I missed to add a line in the code. I noticed that after I replied to you, and therefore I edited the reply .
Regarding the new code, everything works great, except of refreshing range "AJ:AJ" in worksheet "2014" with the new values, every time I re-run the macro after making changes in my registrations or the list of exceptions.

And now I noticed that is missing the line

Please Login or Register  to view this content.

in the new code . So everything is perfect.

Thank you again, you genius!

[Bernie Deitrick]

I forgot to add this line to the second version:

Please Login or Register  to view this content.

But it should be changed anyway, to clear out the old values

Please Login or Register  to view this content.

[boboivan]

Yeah, now is totally perfect!

I was thinking last night if the code could be improved so as to display the missing numbers from range B:B, sheet ("2014") directly to range AJ:AJ, sheet ("2014"), without creating a new sheet ("MissingNumbers").

Do you think is this possible? Thx again and sorry for the trouble.

Meanwhile, I found a solution which works ok, but I don't think it's a very elegant one. What do you think of it?

Please Login or Register  to view this content.