Add numbers missing from 1-30 in a list

Luke,

Give this a try:

Sub tgr()
    
    Const lStartNum As Long = 1
    Const lEndNum As Long = 30
    
    Dim arrNums As Variant
    Dim varNum As Variant
    Dim strNums As String
    Dim lSize As Long
    
    strNums = Join(Application.Transpose(Evaluate("ROW(" & lStartNum & ":" & lEndNum & ")")), " ")
    For Each varNum In Application.Transpose(Range("A2", Cells(Rows.Count, "A").End(xlUp)).Value)
        strNums = Replace(" " & strNums & " ", " " & varNum & " ", " ")
    Next varNum
    arrNums = Split(WorksheetFunction.Trim(strNums), " ")
    lSize = UBound(arrNums) - LBound(arrNums) + 1
    
    If lSize > 0 Then Cells(Rows.Count, "A").End(xlUp).Offset(1).Resize(lSize).Value = Application.Transpose(arrNums)
    
End Sub

Hi,

Although this doesn't list the missing numbers immediately underneath each other it will identify them. You could then copy and paste as values then sort them.

In B2 copied to B30

Formula:

=IF(ISERROR(VLOOKUP(ROW(A2),$A$2:$A$31,1,FALSE)),ROW(A2),"")

Hi,

You could of course encapsulate the method in a procedure. i.e.

Sub IDMissingNumbers()
    Dim x As Long
    x = Range("A2").CurrentRegion.Rows.Count - 1
    Range("B2") = "=IF(ISERROR(VLOOKUP(ROW(A2),$A$2:$A$30,1,FALSE)),ROW(A2),"""")"
    Range("B2").Copy Destination:=Range("B2:B31")
    Range("B2:B31").Copy
    Range("A100").End(xlUp).Cells(2, 1).PasteSpecial (xlPasteValues)
    Range("A" & x + 2 & ":A60").Sort key1:=Range("A" & x + 2), Header:=xlNo
    Range("B2:B31").Clear
End Sub

If you'll never change the parameters away from looking for "1 to 30", this condensed version will also work, though it may be less readable...

Sub tgr()
    
    Dim arrNums As Variant
    Dim lSize As Long
    
    arrNums = Filter(Application.Transpose([IF(COUNTIF(A:A,ROW(1:30))=0,ROW(1:30))]), False, False)
    lSize = UBound(arrNums) - LBound(arrNums) + 1
    
    If lSize > 0 Then
        With Cells(Rows.Count, "A").End(xlUp).Offset(1).Resize(lSize)
            .Value = Application.Transpose(arrNums)
            .Font.Color = vbWhite
        End With
    End If
    
End Sub

PM received from lukestkd:

Originally Posted by lukestkd

Hi TigerAvatar,
A quick question. What code would I need to add in to set the font colour of the numbers added to white?

I have ActiveSheet.Font.Color = vbWhite

But I'm not sure how to only apply it to the numbers that have been added in?

If its not possible it's ok - it'd just neaten it up visually

Many thanks!

Luke

First the ugly part From the Forum Rules:
4. Don't Private Message or email Excel questions to moderators or other members. (or Word, Access, etc.) The point of having a public forum is to share solutions to common (and sometimes uncommon) problems with all members.


Now that that's out of the way, replace this line of code:

    If lSize > 0 Then Cells(Rows.Count, "A").End(xlUp).Offset(1).Resize(lSize).Value = Application.Transpose(arrNums)

With this instead:

    If lSize > 0 Then
        With Cells(Rows.Count, "A").End(xlUp).Offset(1).Resize(lSize)
            .Value = Application.Transpose(arrNums)
            .Font.Color = vbWhite
        End With
    End If