Find the missing numbers from the range of numbers and filling the missing number in the cells below the last row

[sekar]

hi.

referring below sheet how to find the missing numbers from the range of cells, for example, the last number is 600 ( to be input by the user). The missing numbers has to be filled below the last row of the existing column.

No.
426
430
432
435
436
438
439
441
442
443
445
446
447
448
449
424
425
427
428
429
431
433
434
437
440
444
590
591
592
593
594
595
596
597
598
600
599
584
586
587
588
589
582
583
585
397
398
399
401
408
411
415
396
409
410
412
413
414
419
423
400
407
416
417
418
422
402
403
404
405
406
420
421
345
346
361
367
368
369
370
371
372
373
374
375
384
347
348
349
350
352
353
355
357
358
360
377
378
379
380
351
354
356
359
362
363
364
365
366
376
381
382
383
385
386
387
388
510
511
512
513
520
521
522
523
509
514
515
516
517
518
519
524
525
526
532
541
547
534
535
536
538
539
540
545
531
533
537
546
530
542
543
544
550
551
552
553
556
548
549
554
555
557
561
567
559
564
565
566
569
570
574
575
577
558
560
562
563
568
571
572
573
576
578
579
580
7
8
33
34
39
40
63
66
67
69
71
87
88
94
9
42
45
57
64
65
68
70
96
15
17
18
21
22
23
28
43
44
48
56
78
93
95
10
11
13
14
29
54
55
72
76
77
79
80
81
82
1
5
6
12
46
47
49
50
52
59
61
62
73
74
91
92
2
3
19
20
24
25
30
31
32
37
38
41
51
58
60
75
83
84
85
86
90
4
16
26
27
35
36
53
89
97
98
99
126
132
213
245
254
260
262
272
278
298
122
134
139
144
205
244
275
299
138
195
229
230
255
273
274
296
101
118
143
221
222
289
290
294
100
110
115
116
127
133
198
238
271
277
292
295
297
147
157
158
159
160
177
180
187
188
193
194
204
235
236
286
135
136
161
163
178
189
197
211
212
237
243
257
268
269
103
119
120
128
141
142
173
190
199
200
220
253
259
270
276
291
293
102
162
172
191
192
208
209
219
228
234
250
251
256
104
105
117
121
137
140
146
203
252
258
109
111
164
165
181
182
186
232
242
124
129
130
145
168
169
171
174
175
184
185
196
201
202
223
224
231
233
123
131
179
183
241
246
247
263
264
106
107
112
113
125
166
167
216
239
240
265
266
267
279
114
150
151
156
207
210
214
217
218
226
227
248
249
261
280
284
108
148
149
152
153
154
155
170
176
206
215
225
281
282
283
285
287
288
320
325
327
328
336
337
342
313
317
319
329
343
344
324
331
332
334
335
339
341
318
326
340
323
312
314
315
316
321
322
333
338
450
451
455
456
466
467
468
469
470
476
477
452
457
458
459
460
461
462
463
464
465
471
472
473
474
475
478
479
453
454
480
502
503
504
505
506
507
508

How to do in the macro vba
Thanks

 

[duggie33]

Hi,

You could try this...

Sub ListMissingNumbers()

    Dim lngLastRow As Long
    lngLastRow = Sheet2.Cells(Rows.Count, 1).End(xlUp).Row
   
    Dim rngNumbers As Range
    Set rngNumbers = Sheet2.Range("A2:A" & lngLastRow)
       
    Dim lngMaxNumber As Long
    lngMaxNumber = WorksheetFunction.Max(rngNumbers)

    Dim i As Long

    Dim rngFound As Range

    Dim j As Long
    j = 1

    For i = 1 To lngMaxNumber
        Set rngFound = rngNumbers.Find(i, , xlValues)
        If rngFound Is Nothing Then
            'write the number to end of the list
            Sheet2.Cells(lngLastRow + j, 1).Value = i
            j = j + 1
        End If
    Next i
   

End Sub

Hope that helps,

Doug

 

[sekar]

@Dougg.,

Thanks for providing a wonderful script

 

[duggie33]

You're welcome, glad to help.

Doug

 

[sekar]

Hi @ dougg
Can this data can be copied to another workbook and pasted at the specified cell say "O7".
Also note the rows are dynamic. The number of rows will vary.

I tried, while activating another workbook, the clipboard goes off.

 

[duggie33]

I am not sure what data you want to copy to another workbook. The code will work for data in column A on Sheet2, if you want it to find missing numbers in a different column or a different sheet the code has to be adjusted accordingly. The code finds the last row of column A and sets the range to evaluate from row 2 to the last row.

You should be able to select cells and paste them into a different worksheet. It seems unlikely, but there is a chance that a macro runs and disables the Application.CutCopyMode.

Doug

 

[sekar]

Ok. Thanks