Maccro to insert rows for missing numbers?

[tonywatsonhelp]

HI Everyone,

OK so column A is supposed to be a list of numbers 1 to 7 repeated over and over
so: "1,2,3,4,5,6,7,1,2,3,4,5,6,7,1,2,3,4,5,6,7,1,2,3,4,5,6,7,1,2,3,4,5,6,7, and so on down column A

the problem is some of the numbers are missing for example
"1,2,3,4,5,6,7,1,3,4,5,6,7,1,2,3,5,6,7,1,2,4,5,6,7,1,2,3,4,5,6,7," so I need a macro that can look down column A and if it finds a number missing insert a row and add the missing number,
I'm totally lost could really do with some help here.

Thanks

Tony

 

[MickG]

Try this:-

[COLOR="Navy"]Sub[/COLOR] MG09Jun21
[COLOR="Navy"]Dim[/COLOR] Lst [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] n [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] temp [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
Lst = Range("A" & Rows.Count).End(xlUp).Row
[COLOR="Navy"]For[/COLOR] n = Lst To 2 [COLOR="Navy"]Step[/COLOR] -1
    [COLOR="Navy"]With[/COLOR] Range("A" & n)
        temp = .Value
        temp = IIf(temp = 1, 7, temp - 1)
        [COLOR="Navy"]If[/COLOR] Not .Offset(-1).Value = temp [COLOR="Navy"]Then[/COLOR]
            .EntireRow.Insert
                .Offset(-1).Value = temp
                  n = n + 1
            [COLOR="Navy"]End[/COLOR] If
        
    [COLOR="Navy"]End[/COLOR] With
[COLOR="Navy"]Next[/COLOR] n
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick