R
Rick
The following code (see below) I run after I paste a list
of used telephone extensions into column A. The range of
numbers is 6000 to 6299. Based on the column A data, the
code returns a list of the unused extensions (the gaps in
the data list).
So if I paste in column A:
6000
6002
6005
..
..
..
it will display in column C:
6001
6003
6004
etc
The problem I am having is that the original list I paste
in can have virtual numbers, such as 6*002 and 60*03. For
my purposes, these numbers do not need to be counted. My
current code crashes though until I manually delete these
numbers from the original list - which can take a lot of
time.
Does anyone know of a piece of code that I can run before
the main code below that will automatically delete any
number that has a * character from the list in column A?
Once I have a clean list of numbers, I can then run the
code below as I normally do. If it's easier to have this
newly edited list displayed in column B, that would be
fine too.
Any help on this would be fantastic.
Thanks.
-----------
Sub DisplayMissing_150()
Dim C As Range, V As Variant
Dim prev&, k&, n&
k = 1
prev = 5999 ' one less than beginning, here 6000
For Each C In Intersect(Range("A:A"),
ActiveSheet.UsedRange)
If C > prev + 1 Then ' some numbers left
V = Evaluate("Row(" & prev + 1 & ":" & C - 1 & ")")
n = C - (prev + 1)
Cells(k, "C").Resize(n, 1) = V
k = k + n
End If
prev = C
Next C
' do the last ones, aka from the highest to 6299
If prev < 6299 Then
V = Evaluate("Row(" & prev + 1 & ":6299)")
n = 6299 - prev
Cells(k, "C").Resize(n, 1) = V
End If
End Sub
of used telephone extensions into column A. The range of
numbers is 6000 to 6299. Based on the column A data, the
code returns a list of the unused extensions (the gaps in
the data list).
So if I paste in column A:
6000
6002
6005
..
..
..
it will display in column C:
6001
6003
6004
etc
The problem I am having is that the original list I paste
in can have virtual numbers, such as 6*002 and 60*03. For
my purposes, these numbers do not need to be counted. My
current code crashes though until I manually delete these
numbers from the original list - which can take a lot of
time.
Does anyone know of a piece of code that I can run before
the main code below that will automatically delete any
number that has a * character from the list in column A?
Once I have a clean list of numbers, I can then run the
code below as I normally do. If it's easier to have this
newly edited list displayed in column B, that would be
fine too.
Any help on this would be fantastic.
Thanks.
-----------
Sub DisplayMissing_150()
Dim C As Range, V As Variant
Dim prev&, k&, n&
k = 1
prev = 5999 ' one less than beginning, here 6000
For Each C In Intersect(Range("A:A"),
ActiveSheet.UsedRange)
If C > prev + 1 Then ' some numbers left
V = Evaluate("Row(" & prev + 1 & ":" & C - 1 & ")")
n = C - (prev + 1)
Cells(k, "C").Resize(n, 1) = V
k = k + n
End If
prev = C
Next C
' do the last ones, aka from the highest to 6299
If prev < 6299 Then
V = Evaluate("Row(" & prev + 1 & ":6299)")
n = 6299 - prev
Cells(k, "C").Resize(n, 1) = V
End If
End Sub