| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Delete Row If Cell Does Not Contain "text"
I'm tring to delete a row that has a cell that contains words but does not
contain a specific "text". Ex. Cell A2 = Jill - Good I want to delete Row 2 becasue Cell A2 does not contain "Bad". The Macro is following: Sub Delete Row() Application.ScreenUpdating = False Application.Calculation = xlCalculationManual Dim lastrow As Long, r As Long lastrow = ActiveSheet.UsedRange.Rows.Count For r = lastrow To 1 Step -1 If Cells(r, 3).Value < "text" Then Rows(r).Delete Next r Application.Calculation = xlCalculationAutomatic Application.ScreenUpdating = True End Sub Can somebody show me what is missing or needed to accomplish this? -- Cue |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Delete Row If Cell Does Not Contain "text"
Maybe this approach
Sub delete_It() Dim MyRange1 As Range Dim MyRange As Range lastrow = Cells(Rows.Count, "A").End(xlUp).Row Set MyRange = Range("A1:A" & lastrow) For Each c In MyRange If InStr(1, UCase(c.Value), "BAD", 0) Then If MyRange1 Is Nothing Then Set MyRange1 = c.EntireRow Else Set MyRange1 = Union(MyRange1, c.EntireRow) End If End If Next If Not MyRange1 Is Nothing Then MyRange1.Delete End If End Sub Mike "Cue" wrote: I'm tring to delete a row that has a cell that contains words but does not contain a specific "text". Ex. Cell A2 = Jill - Good I want to delete Row 2 becasue Cell A2 does not contain "Bad". The Macro is following: Sub Delete Row() Application.ScreenUpdating = False Application.Calculation = xlCalculationManual Dim lastrow As Long, r As Long lastrow = ActiveSheet.UsedRange.Rows.Count For r = lastrow To 1 Step -1 If Cells(r, 3).Value < "text" Then Rows(r).Delete Next r Application.Calculation = xlCalculationAutomatic Application.ScreenUpdating = True End Sub Can somebody show me what is missing or needed to accomplish this? -- Cue |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Delete Row If Cell Does Not Contain "text"
I tried your suggestion. It deleted rows with "BAD" in the cell. I want it to
delete those cells that don't have "BAD" in the cell. Do you have another suggestion? -- Cue "Mike H" wrote: Maybe this approach Sub delete_It() Dim MyRange1 As Range Dim MyRange As Range lastrow = Cells(Rows.Count, "A").End(xlUp).Row Set MyRange = Range("A1:A" & lastrow) For Each c In MyRange If InStr(1, UCase(c.Value), "BAD", 0) Then If MyRange1 Is Nothing Then Set MyRange1 = c.EntireRow Else Set MyRange1 = Union(MyRange1, c.EntireRow) End If End If Next If Not MyRange1 Is Nothing Then MyRange1.Delete End If End Sub Mike "Cue" wrote: I'm tring to delete a row that has a cell that contains words but does not contain a specific "text". Ex. Cell A2 = Jill - Good I want to delete Row 2 becasue Cell A2 does not contain "Bad". The Macro is following: Sub Delete Row() Application.ScreenUpdating = False Application.Calculation = xlCalculationManual Dim lastrow As Long, r As Long lastrow = ActiveSheet.UsedRange.Rows.Count For r = lastrow To 1 Step -1 If Cells(r, 3).Value < "text" Then Rows(r).Delete Next r Application.Calculation = xlCalculationAutomatic Application.ScreenUpdating = True End Sub Can somebody show me what is missing or needed to accomplish this? -- Cue |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Delete Row If Cell Does Not Contain "text"
I misread your post,
substitute this line in the macro If InStr(1, UCase(c.Value), "BAD", 0) = False Then Mike "Cue" wrote: I tried your suggestion. It deleted rows with "BAD" in the cell. I want it to delete those cells that don't have "BAD" in the cell. Do you have another suggestion? -- Cue "Mike H" wrote: Maybe this approach Sub delete_It() Dim MyRange1 As Range Dim MyRange As Range lastrow = Cells(Rows.Count, "A").End(xlUp).Row Set MyRange = Range("A1:A" & lastrow) For Each c In MyRange If InStr(1, UCase(c.Value), "BAD", 0) Then If MyRange1 Is Nothing Then Set MyRange1 = c.EntireRow Else Set MyRange1 = Union(MyRange1, c.EntireRow) End If End If Next If Not MyRange1 Is Nothing Then MyRange1.Delete End If End Sub Mike "Cue" wrote: I'm tring to delete a row that has a cell that contains words but does not contain a specific "text". Ex. Cell A2 = Jill - Good I want to delete Row 2 becasue Cell A2 does not contain "Bad". The Macro is following: Sub Delete Row() Application.ScreenUpdating = False Application.Calculation = xlCalculationManual Dim lastrow As Long, r As Long lastrow = ActiveSheet.UsedRange.Rows.Count For r = lastrow To 1 Step -1 If Cells(r, 3).Value < "text" Then Rows(r).Delete Next r Application.Calculation = xlCalculationAutomatic Application.ScreenUpdating = True End Sub Can somebody show me what is missing or needed to accomplish this? -- Cue |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Delete Row If Cell Does Not Contain "text"
Perfecto!
Thanks Mike! -- Cue "Mike H" wrote: I misread your post, substitute this line in the macro If InStr(1, UCase(c.Value), "BAD", 0) = False Then Mike "Cue" wrote: I tried your suggestion. It deleted rows with "BAD" in the cell. I want it to delete those cells that don't have "BAD" in the cell. Do you have another suggestion? -- Cue "Mike H" wrote: Maybe this approach Sub delete_It() Dim MyRange1 As Range Dim MyRange As Range lastrow = Cells(Rows.Count, "A").End(xlUp).Row Set MyRange = Range("A1:A" & lastrow) For Each c In MyRange If InStr(1, UCase(c.Value), "BAD", 0) Then If MyRange1 Is Nothing Then Set MyRange1 = c.EntireRow Else Set MyRange1 = Union(MyRange1, c.EntireRow) End If End If Next If Not MyRange1 Is Nothing Then MyRange1.Delete End If End Sub Mike "Cue" wrote: I'm tring to delete a row that has a cell that contains words but does not contain a specific "text". Ex. Cell A2 = Jill - Good I want to delete Row 2 becasue Cell A2 does not contain "Bad". The Macro is following: Sub Delete Row() Application.ScreenUpdating = False Application.Calculation = xlCalculationManual Dim lastrow As Long, r As Long lastrow = ActiveSheet.UsedRange.Rows.Count For r = lastrow To 1 Step -1 If Cells(r, 3).Value < "text" Then Rows(r).Delete Next r Application.Calculation = xlCalculationAutomatic Application.ScreenUpdating = True End Sub Can somebody show me what is missing or needed to accomplish this? -- Cue |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Delete Row If Cell Does Not Contain "text"
your welcome and thanks for the feedback. I trust my misread didn't trash too
much data :) Mike "Cue" wrote: Perfecto! Thanks Mike! -- Cue "Mike H" wrote: I misread your post, substitute this line in the macro If InStr(1, UCase(c.Value), "BAD", 0) = False Then Mike "Cue" wrote: I tried your suggestion. It deleted rows with "BAD" in the cell. I want it to delete those cells that don't have "BAD" in the cell. Do you have another suggestion? -- Cue "Mike H" wrote: Maybe this approach Sub delete_It() Dim MyRange1 As Range Dim MyRange As Range lastrow = Cells(Rows.Count, "A").End(xlUp).Row Set MyRange = Range("A1:A" & lastrow) For Each c In MyRange If InStr(1, UCase(c.Value), "BAD", 0) Then If MyRange1 Is Nothing Then Set MyRange1 = c.EntireRow Else Set MyRange1 = Union(MyRange1, c.EntireRow) End If End If Next If Not MyRange1 Is Nothing Then MyRange1.Delete End If End Sub Mike "Cue" wrote: I'm tring to delete a row that has a cell that contains words but does not contain a specific "text". Ex. Cell A2 = Jill - Good I want to delete Row 2 becasue Cell A2 does not contain "Bad". The Macro is following: Sub Delete Row() Application.ScreenUpdating = False Application.Calculation = xlCalculationManual Dim lastrow As Long, r As Long lastrow = ActiveSheet.UsedRange.Rows.Count For r = lastrow To 1 Step -1 If Cells(r, 3).Value < "text" Then Rows(r).Delete Next r Application.Calculation = xlCalculationAutomatic Application.ScreenUpdating = True End Sub Can somebody show me what is missing or needed to accomplish this? -- Cue |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Delete Row If Cell Does Not Contain "text"
Sub Delete_Row()
'looks only at Column A Application.ScreenUpdating = False Application.Calculation = xlCalculationManual Dim lastrow As Long, r As Long lastrow = ActiveSheet.UsedRange.Rows.Count For r = lastrow To 1 Step -1 If Cells(r, 1).Value < "Bad" Then Rows(r).Delete Next r Application.Calculation = xlCalculationAutomatic Application.ScreenUpdating = True End Sub Gord Dibben MS Excel MVP On Tue, 6 May 2008 11:20:08 -0700, Cue wrote: I'm tring to delete a row that has a cell that contains words but does not contain a specific "text". Ex. Cell A2 = Jill - Good I want to delete Row 2 becasue Cell A2 does not contain "Bad". The Macro is following: Sub Delete Row() Application.ScreenUpdating = False Application.Calculation = xlCalculationManual Dim lastrow As Long, r As Long lastrow = ActiveSheet.UsedRange.Rows.Count For r = lastrow To 1 Step -1 If Cells(r, 3).Value < "text" Then Rows(r).Delete Next r Application.Calculation = xlCalculationAutomatic Application.ScreenUpdating = True End Sub Can somebody show me what is missing or needed to accomplish this? |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Excel "Move or Copy" and "Delete" sheet functions | Excel Worksheet Functions | |||
| Macro to Replace/Delete Text Using "Watchword" List? | Excel Discussion (Misc queries) | |||
| USING EXCEL, delete all text right of the "!" symbol | Excel Worksheet Functions | |||
| Excel: Changing "numeric $" to "text $" in a different cell. | Excel Worksheet Functions | |||
| Insert "-" in text "1234567890" to have a output like this"123-456-7890" | Excel Discussion (Misc queries) | |||
Linear Mode
