Finding next available number

[FRIEL]

Hi all =D

I am trying to make a macro to tell me the next lowest unit number available in a parts list.
I have unit numbers listed down Multiple times.
The unit numbers start at 100 and go up in increments of one, but not necessary in order and some may be missed/removed so these numbers need to be reused.

There are gaps of 1 row to split up different parts.
Its probably not all that clear explaining it so i will attach a Dummy workbook too.

Attachment 247577

[HaHoBe]

Hi, FRIEL,

canīt download the attachment. Maybe you can use WorksheetFunction.Max(Range("$A$2:$A400")) + 1 for solving the problem where I assumed the list to be in Column A. Suit the upper row number to match one row less than the new one should be added.

Ciao,
Holger

[FRIEL]

that attachment hasnt worked.

example.xlsx

this should work

[Marcol]

The link to your attachment is invalid (Post #1).

Please try again.

[EDIT]
Okay, it's fine in Post#3

[FRIEL]

cant use Max as it isn't always going to be the largest number next.
if i remove unit 105, for instance, then unit 105 will be the next available number.

[FRIEL]

cant use Max as it isn't always going to be the largest number next.
if i remove unit 105, for instance, then unit 105 will be the next available number.

i think the attachment works now.
unfortunately my fire walls wont let me download anything from here so i cant check

[FRIEL]

cant use Max as it isn't always going to be the largest number next.
if i remove unit 105, for instance, then unit 105 will be the next available number.

i think the attachment works now.
unfortunately my fire walls wont let me download anything from here so i cant check

[HaHoBe]

Hi, FRIEL,

I realized that as well when looking at the workbook and reading the question again.

Please Login or Register  to view this content.

100 is not present, 124 would be the next number if you change 100 to 101.

Ciao,
Holger

[FRIEL]

oh yeah, that's my bad it starts at 101

working exactly as i wanted.

thanks HaHoBe