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

Maybe applying data|filter|autofilter, filtering to show only those cells that
contain ~* (* is a wild card, the ~ means to look for the real asterisk), then
delete the visible rows:

Option Explicit

Sub testme()
Dim wks As Worksheet

Set wks = ActiveSheet

With wks
.AutoFilterMode = False
.Range("a:a").AutoFilter Field:=1, Criteria1:="=*~**"
With .AutoFilter.Range
If .Columns(1).Cells.SpecialCells(xlCellTypeVisible) _
.Cells.Count 1 Then
.Resize(.Rows.Count - 1, 1).Offset(1, 0) _
.Cells.SpecialCells(xlCellTypeVisible).EntireRow.D elete
End If
End With
.AutoFilterMode = False
End With

End Sub

Rick wrote:

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

--

Dave Peterson

Thanks for your help with this, Dave. Your code worked
perfectly.
Thanks again.


-----Original Message-----
Maybe applying data|filter|autofilter, filtering to show
only those cells that
contain ~* (* is a wild card, the ~ means to look for the
real asterisk), then
delete the visible rows:

Option Explicit

Sub testme()
Dim wks As Worksheet

Set wks = ActiveSheet

With wks
.AutoFilterMode = False
.Range("a:a").AutoFilter Field:=1,
Criteria1:="=*~**"
With .AutoFilter.Range
If .Columns(1).Cells.SpecialCells
(xlCellTypeVisible) _
.Cells.Count 1 Then
.Resize(.Rows.Count - 1, 1).Offset(1, 0) _
.Cells.SpecialCells
(xlCellTypeVisible).EntireRow.Delete
End If
End With
.AutoFilterMode = False
End With

End Sub

Rick wrote:

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

--

Dave Peterson

.