The attached macro, which finds the missing, numbers from 1 to 30. I got it from under this link...

### Find missing numbers from random numbers list

I have a huge list of numbers which consists of random numbers from 1 to 100000 like 1,2,5,6 etc. I want to find out the numbers which are not in the list, i.e. 3,4 in above example. How can I do it?

www.mrexcel.com

VBA Code:

```
Option Explicit
Sub MissingValues()
Const MAX_NUMBERS = 30 ' 100000 ' Maxmimum value expected
Const MAX_ROWS = 30 ' 3000 ' Number of rows in output list
' before using next column
Dim blnNumbers(1 To MAX_NUMBERS) As Boolean
Dim lngRow As Long
Dim rngData As Range
Dim lngIndex As Long
Dim intCol As Integer
' mark all existing numbers as true
For Each rngData In Range(Range("A1"), Range("A65536").End(xlUp))
blnNumbers(rngData.Value) = True
Next
' output list(s) of missing values
intCol = 2
For lngIndex = 1 To MAX_NUMBERS
If Not blnNumbers(lngIndex) Then
lngRow = lngRow + 1
Cells(lngRow, intCol) = lngIndex
If lngRow = MAX_ROWS Then
lngRow = 0
intCol = intCol + 1
If intCol > 256 Then
MsgBox "No more space", vbExclamation
Exit Sub
End If
End If
End If
Next
End Sub
```

For example in the column "A" there are some random, macro find missing numbers in range 1 to 30 and list them in the column B As show in the example below with the sheet 1.

Example Sheet1

1 | 4 |

2 | 5 |

3 | 6 |

10 | 7 |

11 | 8 |

12 | 9 |

13 | 15 |

14 | 16 |

22 | 17 |

26 | 18 |

19 | |

20 | |

21 | |

23 | |

24 | |

25 | |

27 | |

28 | |

29 | |

30 |

Please I need help to modify the missing numbers macro.

1-Which can find missing number from the column "A" and list them in to column "E"

2- can find missing number from the column "B" and list them in to column "F"

3- can find missing number from the column "C" and list them in to column "G"

As show in the example below with the sheet 2.

Example Sheet2

1 | 22 | 27 | 4 | 4 | 6 | |

2 | 22 | 29 | 5 | 6 | 7 | |

3 | 27 | 29 | 6 | 7 | 9 | |

10 | 11 | 4 | 7 | 8 | 15 | |

11 | 5 | 4 | 8 | 9 | 16 | |

12 | 25 | 12 | 9 | 15 | 17 | |

13 | 22 | 22 | 15 | 16 | 18 | |

14 | 19 | 24 | 16 | 17 | 20 | |

22 | 5 | 19 | 17 | 18 | 21 | |

30 | 14 | 8 | 18 | 20 | 23 | |

10 | 4 | 19 | 21 | 26 | ||

6 | 7 | 20 | 23 | 28 | ||

25 | 21 | 24 | ||||

14 | 23 | 26 | ||||

29 | 24 | 28 | ||||

25 | 29 | |||||

26 | ||||||

27 | ||||||

28 | ||||||

29 |

Thank you all.

I am using Excel 2000

Regards,

Moti