#### TAPS_MikeDion

##### Well-known Member

- Joined
- Aug 14, 2009

- Messages
- 524

- Office Version
- 2011

- Platform
- MacOS

Below is the code I'm using (it does work) to find the next available number in a series of numbers in column A. I search through column A, find the missing numbers, put them into an array and then find the smallest number in the array.

Is there is a more simplified/efficient way of doing it?

Thanks!

xNum = existing number

mNum = missing number

mArr() = missing numbers array

NextEmpNum = next employee number

Code:

```
Dim x As Long, x2 As Long
Dim xNum As Long, mNum As Long
Dim Found As Boolean
Dim mArr() As Integer
Set ws = Sheets("DataSheet")
LastRow = ws.Range("A" & ws.Rows.Count).End(xlUp).Row
With ws
xNum = 0
mNum = -1
For x = 2 To LastRow
Found = False
xNum = xNum + 1
For x2 = 2 To LastRow
If xNum = Cells(x2, 1) Then Found = True
Next x2
If Not Found Then
mNum = mNum + 1
ReDim Preserve mArr(mNum)
mArr(mNum) = xNum
End If
Next x
If mNum > 1 Then
NextEmpNum = WorksheetFunction.Min(mArr)
Else
NextEmpNum = 1
End If
End With
```