Missing Numbers from a Range
 

Hello All

Hope one of you can help, I need to create a macro that can identify
missing nos. from a range I.e
Range Missing no.
1 4
2 6
3
5
7

Thanks in advance


--
robmeister
------------------------------------------------------------------------
robmeister's Profile: http://www.excelforum.com/member.php...o&userid=24364
View this thread: http://www.excelforum.com/showthread...hreadid=379607

Missing Numbers from a Range
 

Assuming your first column starts from A1, run this macro:

Private Sub CommandButton1_Click()

Set rngStart = Range("A1")
Set rngResult = Range("B1")

Set rngSearch = Range(rngStart, rngStart.End(xlDown))
j = 0
i = 1

For Each cl In rngSearch
If i 1 Then
diff = rngSearch(i) - rngSearch(i - 1)
If diff 1 Then
For k = 2 To diff
rngResult.Offset(j, 0) = rngSearch(i - 1) + k - 1
j = j + 1
Next
End If
End If
i = i + 1
Next
End Sub



Mangesh


--
mangesh_yadav
------------------------------------------------------------------------
mangesh_yadav's Profile: http://www.excelforum.com/member.php...o&userid=10470
View this thread: http://www.excelforum.com/showthread...hreadid=379607

Missing Numbers from a Range
 

Thank you Mangesh that's worked.
CHeers :)


--
robmeister
------------------------------------------------------------------------
robmeister's Profile: http://www.excelforum.com/member.php...o&userid=24364
View this thread: http://www.excelforum.com/showthread...hreadid=379607

Missing Numbers from a Range
 

Glad it helped. Thanks for the feedback.

Manges

--
mangesh_yada
-----------------------------------------------------------------------
mangesh_yadav's Profile: http://www.excelforum.com/member.php...fo&userid=1047
View this thread: http://www.excelforum.com/showthread.php?threadid=37960