Macro For Deleting Cells Containing A Number

[brazen234]

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!

[Yngve]

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

[Gary Keramidas]

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" <[email protected]> wrote
in message news:[email protected]...
>
> 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
>

[Toppers]

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
>
>

[brazen234]

-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.

[Yngve]

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

[Toppers]

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
>
>

[Gary Keramidas]

adding this would make it a little faster

Application.ScreenUpdating = False

code

Application.ScreenUpdating = true
--


Gary


"brazen234" <[email protected]> wrote
in message news:[email protected]...
>
> -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
>

[JMay]

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
news:[email protected]...
> adding this would make it a little faster
>
> Application.ScreenUpdating = False
>
> code
>
> Application.ScreenUpdating = true
> --
>
>
> Gary
>
>
> "brazen234" <[email protected]> wrote in
> message news:[email protected]...
>>
>> -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
>>
>
>

[Gary Keramidas]

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" <[email protected]> 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
> news:[email protected]...
>> adding this would make it a little faster
>>
>> Application.ScreenUpdating = False
>>
>> code
>>
>> Application.ScreenUpdating = true
>> --
>>
>>
>> Gary
>>
>>
>> "brazen234" <[email protected]>
>> wrote in message
>> news:[email protected]...
>>>
>>> -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
>>>
>>
>>
>
>