Macro For Deleting Cells Containing A Number
 

Here is an example of the list in Column A:

jswl
jyqx
jzrk
k088
k508
k556
k558
ka30
kfrd
kjkg
klak


I would like to create a macro that will delete ONLY the cells tha
contain a number. One number or or more, doesn't matter. If it has
number it has got to go!

Thanks

--
brazen23
-----------------------------------------------------------------------
brazen234's Profile: http://www.excelforum.com/member.php...nfo&userid=995
View this thread: http://www.excelforum.com/showthread.php?threadid=50147

Macro For Deleting Cells Containing A Number
 

Hi brazen234
Sub A()

Dim i As Double
For i = Cells(Rows.Count, "A").End(xlUp).Row To 1 Step -1
If IsNumeric(Cells(i, "A").Value) Then
Cells(i, "A") = "" ' This empty cell
'Rows(i & ":" & i).Delete Shift:=xlUp ' remowe rows
End If
Next i

End Sub

Regards yngve

Macro For Deleting Cells Containing A Number
 

here's one way, i am sure there are other shorter ways

Option Explicit
Dim arr As Variant
Dim i As Long
Dim lastRow As Long
Dim rng As Range
Dim cell As Range
Dim c As Long
Sub find_Number()
arr = Array("0", "1", "2", "3", "4", "5", "6", "7", "8", "9")
lastRow = Worksheets("Sheet1").Cells(Rows.Count, "A").End(xlUp).Row
Set rng = Range("A1:A" & lastRow)
For i = LBound(arr) To UBound(arr)
c = lastRow
Do While c 0
If InStr(1, Range("a" & c), arr(i)) Then
Rows(c).EntireRow.Delete
Else
End If
c = c - 1
Loop
Next
End Sub


--


Gary


"brazen234" wrote
in message ...

Here is an example of the list in Column A:

jswl
jyqx
jzrk
k088
k508
k556
k558
ka30
kfrd
kjkg
klak


I would like to create a macro that will delete ONLY the cells that
contain a number. One number or or more, doesn't matter. If it has a
number it has got to go!

Thanks!


--
brazen234
------------------------------------------------------------------------
brazen234's Profile:
http://www.excelforum.com/member.php...fo&userid=9952
View this thread: http://www.excelforum.com/showthread...hreadid=501478

Macro For Deleting Cells Containing A Number
 

Assuming the last character is numeric:

Sub A()

Dim i As Double
For i = Cells(Rows.Count, "A").End(xlUp).Row To 1 Step -1
If IsNumeric(Right(Cells(i, "A").Value, 1)) Then
'Cells(i, "A") = "" ' This empty cell
Rows(i & ":" & i).Delete Shift:=xlUp ' remowe rows
End If
Next i

End Sub

"brazen234" wrote:


Here is an example of the list in Column A:

jswl
jyqx
jzrk
k088
k508
k556
k558
ka30
kfrd
kjkg
klak


I would like to create a macro that will delete ONLY the cells that
contain a number. One number or or more, doesn't matter. If it has a
number it has got to go!

Thanks!


--
brazen234
------------------------------------------------------------------------
brazen234's Profile: http://www.excelforum.com/member.php...fo&userid=9952
View this thread: http://www.excelforum.com/showthread...hreadid=501478

Macro For Deleting Cells Containing A Number
 

-Yngve
I ran your macro but it did not delete the rows with numbers. The lis
was the same after running it.

-Gary Keramidas
Thanks, your macro does work! But can it be faster ? My lists are i
the tens of thousands.

-Toppers
Thanks but there are also occurences of the last character being alph
with numbers inbetween

--
brazen23
-----------------------------------------------------------------------
brazen234's Profile: http://www.excelforum.com/member.php...nfo&userid=995
View this thread: http://www.excelforum.com/showthread.php?threadid=50147

Macro For Deleting Cells Containing A Number
 

Hi
Toppers, you are right, "overlook`t" it should have been
Sub A()

Dim i As Double

For i = Cells(Rows.Count, "A").End(xlUp).Row To 1 Step -1


If Val(Right(Cells(i, "A"), 1)) 0 _
Or Val(Right(Cells(i, "A"), 2)) 0 _
Or Val(Right(Cells(i, "A"), 3)) 0 Then
Cells(i, "A") = Empty
End If
Next i

End Sub

Regards Yngve

Macro For Deleting Cells Containing A Number
 

Try this which processed 10000 rows in les 0.2 seconds.

Sub DeleteX()

Dim i As Double, x As Variant
Application.ScreenUpdating = False
st = Timer
n = Cells(Rows.Count, "A").End(xlUp).Row
x = Range("a1:a" & n)

k = 0
For i = 1 To UBound(x, 1)
For j = 1 To Len(x(i, 1))
If IsNumeric(Mid(x(i, 1), j, 1)) Then
x(i, 1) = ""
Exit For
End If
Next j
Next i

Range("a1:a" & n) = x
Columns("A:A").Select
Selection.Sort Key1:=Range("A1"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
Application.ScreenUpdating = True
ft = Timer
MsgBox ft - st


"Yngve" wrote:

Hi
Toppers, you are right, "overlook`t" it should have been
Sub A()

Dim i As Double

For i = Cells(Rows.Count, "A").End(xlUp).Row To 1 Step -1


If Val(Right(Cells(i, "A"), 1)) 0 _
Or Val(Right(Cells(i, "A"), 2)) 0 _
Or Val(Right(Cells(i, "A"), 3)) 0 Then
Cells(i, "A") = Empty
End If
Next i

End Sub

Regards Yngve

Macro For Deleting Cells Containing A Number
 

adding this would make it a little faster

Application.ScreenUpdating = False

code

Application.ScreenUpdating = true
--


Gary


"brazen234" wrote
in message ...

-Yngve
I ran your macro but it did not delete the rows with numbers. The list
was the same after running it.

-Gary Keramidas
Thanks, your macro does work! But can it be faster ? My lists are in
the tens of thousands.

-Toppers
Thanks but there are also occurences of the last character being alpha
with numbers inbetween.


--
brazen234
------------------------------------------------------------------------
brazen234's Profile:
http://www.excelforum.com/member.php...fo&userid=9952
View this thread: http://www.excelforum.com/showthread...hreadid=501478

Macro For Deleting Cells Containing A Number
 

Another modification (to speed it up) would be to after each trip Up the column
and if deletion takes place have
the macro recalculate the "lastrow" integer. At present, it is agter each trip
Up the column returning to Row 11 when in fact there is no data in row 11, 10,
9, 8 --- if 4 rows have already been deleted.
I changed your reference to lastrow to BegLastRow
and then put within the For Next statement a Recalculation
statement and a new Reference NewLastRow to
eliminate revisiting already deleted rows.

Sub Foo()
Dim arr As Variant
Dim i As Long
Dim BlastRow As Long
Dim rng As Range
Dim cell As Range
Dim c As Long
arr = Array("0", "1", "2", "3", "4", "5", "6", "7", "8", "9")
BegLastRow = Worksheets("Sheet1").Cells(Rows.Count, "A").End(xlUp).Row
Set rng = Range("A1:A" & BegLastRow)
For i = LBound(arr) To UBound(arr)
Application.Calculate
NewLastRow = Worksheets("Sheet1").Cells(Rows.Count, "A").End(xlUp).Row
c = NewLastRow
Do While c 0
If InStr(1, Range("a" & c), arr(i)) Then
Rows(c).EntireRow.Delete
Else
End If
c = c - 1
Loop
Next
End Sub

Logically, this involves fewer passes. Gary Did an excellent job on this
incidently; Thanks Gary...

HTH,

Jim May

"Gary Keramidas" <GKeramidasATmsn.com wrote in message
...
adding this would make it a little faster

Application.ScreenUpdating = False

code

Application.ScreenUpdating = true
--


Gary


"brazen234" wrote in
message ...

-Yngve
I ran your macro but it did not delete the rows with numbers. The list
was the same after running it.

-Gary Keramidas
Thanks, your macro does work! But can it be faster ? My lists are in
the tens of thousands.

-Toppers
Thanks but there are also occurences of the last character being alpha
with numbers inbetween.


--
brazen234
------------------------------------------------------------------------
brazen234's Profile:
http://www.excelforum.com/member.php...fo&userid=9952
View this thread: http://www.excelforum.com/showthread...hreadid=501478

Macro For Deleting Cells Containing A Number
 

jim:

thanks for that tip, and the compliment. don't know how good it is, i just
muddle my way through most of the time

thanks again

--


Gary


"JMay" wrote in message
news:J5yyf.138948$WH.98313@dukeread01...
Another modification (to speed it up) would be to after each trip Up the
column and if deletion takes place have
the macro recalculate the "lastrow" integer. At present, it is agter each
trip Up the column returning to Row 11 when in fact there is no data in
row 11, 10, 9, 8 --- if 4 rows have already been deleted.
I changed your reference to lastrow to BegLastRow
and then put within the For Next statement a Recalculation
statement and a new Reference NewLastRow to
eliminate revisiting already deleted rows.

Sub Foo()
Dim arr As Variant
Dim i As Long
Dim BlastRow As Long
Dim rng As Range
Dim cell As Range
Dim c As Long
arr = Array("0", "1", "2", "3", "4", "5", "6", "7", "8", "9")
BegLastRow = Worksheets("Sheet1").Cells(Rows.Count, "A").End(xlUp).Row
Set rng = Range("A1:A" & BegLastRow)
For i = LBound(arr) To UBound(arr)
Application.Calculate
NewLastRow = Worksheets("Sheet1").Cells(Rows.Count, "A").End(xlUp).Row
c = NewLastRow
Do While c 0
If InStr(1, Range("a" & c), arr(i)) Then
Rows(c).EntireRow.Delete
Else
End If
c = c - 1
Loop
Next
End Sub

Logically, this involves fewer passes. Gary Did an excellent job on this
incidently; Thanks Gary...

HTH,

Jim May

"Gary Keramidas" <GKeramidasATmsn.com wrote in message
...
adding this would make it a little faster

Application.ScreenUpdating = False

code

Application.ScreenUpdating = true
--


Gary


"brazen234"
wrote in message
...

-Yngve
I ran your macro but it did not delete the rows with numbers. The list
was the same after running it.

-Gary Keramidas
Thanks, your macro does work! But can it be faster ? My lists are in
the tens of thousands.

-Toppers
Thanks but there are also occurences of the last character being alpha
with numbers inbetween.


--
brazen234
------------------------------------------------------------------------
brazen234's Profile:
http://www.excelforum.com/member.php...fo&userid=9952
View this thread:
http://www.excelforum.com/showthread...hreadid=501478