Find missing numbers in a column

Using your posted workbook....here's something to try.

This regular formula returns a list of the missing check numbers:

Please Login or Register  to view this content.

Copy that formula down as far as you need.

Note: the 650000 adjustments are necessary because we are using Row Numbers
to simulate your series...and Excel 2003 only has 65,536 rows.

In your example, these values are returned in H2:H4
652001
652003
652004

Is that something you can work with?

This will list the missing Checks in column J:

Please Login or Register  to view this content.

"Rows.Count" means "the last row in the entire workbook". This is different for different versions of Excel.

"Range("A" & Rows.Count)" means "the last cell in column A in the entire workbook..." In Excel 2003 that's A65536.

"Range("A" & Rows.Count).End(xlUp)" means "start at the last cell in column A and jump UP to the last cell with data in it.

LastRow = Range("A" & Rows.Count).End(xlUp).Row means "remember the ROW# of the last cell in column A with data in it and call it "LastRow" for short, usable as a variable in other VBA code.

===================
If that takes care of your need, be sure to EDIT your original post (Go Advanced) and mark the PREFIX box [SOLVED]

JB, re:

Please Login or Register  to view this content.

i & LastRow are Variants in the above, perhaps you meant ?

Please Login or Register  to view this content.

Someone recently indicated to me that:

Please Login or Register  to view this content.

...was shorthand for:

Please Login or Register  to view this content.

Before that I had always used the latter, and as my formulas all continue to operate as expected when I used the shorthand, I surmised their suggestion was correct.

Prior to that I had always thought that declaring JUST a variable name mean Variant, but this testing made me believe otherwise... (literally just days ago).

So I was right in my original understanding, I DO need to declare in longhand? Why would my numerical(s) variable continue to function with no issues? Usually I get error messages when I use the wrong TYPE of variable.

In VBA

Please Login or Register  to view this content.

only C is Long, a & b are Variants ... whereas in .NET a, b & c would all be Long

As to why your code works ... VBA is designed for non-programmers ie there is the expectation that Variables may not be declared at all - hence Option Explicit is not default setting.
Anything not declared explicitly is assiged a Variant and a Variant can be anything hence the code works regardless...
General school of thought is: Variants = Inefficient/Expensive, however, I've heard MS MVP's say recently (Simon Hodge) that they think that's not really that significant an issue in VBA and that in reality all Cells are Variants (and there are lots of those without impacting performance!).

Note: In .NET, code is Option Explicit and the Variant type no longer exists (one must use Object)