Finding next available number

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

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

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.

that attachment hasnt worked.

example.xlsx

this should work

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

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

Hi, FRIEL,

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

Sub TellAvailNr()
Dim lngFree As Long

lngFree = 100
Do While WorksheetFunction.CountIf(Range("D9:D" & Range("D" & Rows.Count).End(xlUp).Row), lngFree) > 0
  lngFree = lngFree + 1
Loop

MsgBox lngFree
End Sub

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

Ciao,
Holger

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

Please try again.

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

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

working exactly as i wanted.

thanks HaHoBe