excel - Vba to perform gap analysis

Question

My goal is to perform a gap analysis in one excel column that has consecutive numbers and have all missing values copied to a new column. So far the code i have runs well as expected but with one deficiency. The issue is that if i select a column range that genuinely has no missing values i get a runtime error.

Can someone help fix the below vba code so that the program ends gracefully and if there are no missing values then a message box can simply say so as i am not experienced in vba.

Sub missing()

Dim rng As Range
Dim d As Object, a, c()
Dim i As Long, mx As Long, mn As Long

Set rng = Application.InputBox(Prompt:="Select a range:", _
Title:="Extract missing values", _
Default:=Selection.Address, Type:=8)
Set d = CreateObject("scripting.dictionary")
a = rng
mx = Application.Max(a): mn = Application.Min(a)
ReDim c(1 To mx - mn + 1, 1 To 1)

For i = 1 To UBound(a): d(a(i, 1)) = 1: Next i

For i = mn To mx
    If d(i) <> 1 Then k = k + 1: c(k, 1) = i
Next i

Range("B1") = "Missing Values"
Range("B2").Resize(k) = c

End Sub

Thanks in advance

Answer 1

Please, replace:

Range("B2").Resize(k) = c

with:

If k > 0 Then
    Range("H2").Resize(k) = c
Else
    MsgBox "No gaps in the selected range..."
End If

I think, you should mention that your code should process a range having consecutive numbers and it makes the gap analysis to extract the missing ones. I have to deduce this aspect only reading the code...