Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
MACRO HELP - deleting rows containing a range of blank cells | Excel Discussion (Misc queries) | |||
Need Help Creating a Macro to Multiply Cells by a Constant Number | Excel Discussion (Misc queries) | |||
Macro to add cells from unknown number of files | Excel Programming | |||
Deleting Hyphens or Dashes from multiple cells without deleting the remaining content | Excel Programming | |||
deleting variable number of rows | Excel Programming |