Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Marco to delete all rows except those containing 'MZ' in string
I have a macro which does the does the opposite of what I want. It
deletes all the rows that contains MZ in the first column and leaves everything behing. I just want to keep the rows that contain MZ in the first column and delete all the others rows. Any help will be greatly appreciated. Here is what I have; Sub Delete_Row() Dim myWord As String myWord = "MZ" With ActiveSheet On Error Resume Next Do .Cells.Find(What:="MZ", After:=.Cells(.Cells.Count), _ LookIn:=xlFormulas, Lookat:=xlPart, _ SearchOrder:=xlByRows, SearchDirection:=xlNext, _ MatchCase:=True).EntireRow.Delete If Err.Number < 0 Then Exit Do Loop On Error GoTo 0 End With End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Marco to delete all rows except those containing 'MZ' in string
Sub Delete_Row()
Range("A1").Activate Do If Instr(Activecell) = "MZ" then Activecell.Offset(1,0).Activate Else Activecell.EntireRow.Delete Shift:=XLUp End if Loop until Activecell = " End Sub -- Best wishes, Jim " wrote: I have a macro which does the does the opposite of what I want. It deletes all the rows that contains MZ in the first column and leaves everything behing. I just want to keep the rows that contain MZ in the first column and delete all the others rows. Any help will be greatly appreciated. Here is what I have; Sub Delete_Row() Dim myWord As String myWord = "MZ" With ActiveSheet On Error Resume Next Do .Cells.Find(What:="MZ", After:=.Cells(.Cells.Count), _ LookIn:=xlFormulas, Lookat:=xlPart, _ SearchOrder:=xlByRows, SearchDirection:=xlNext, _ MatchCase:=True).EntireRow.Delete If Err.Number < 0 Then Exit Do Loop On Error GoTo 0 End With End Sub |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Marco to delete all rows except those containing 'MZ' in string
Hi Dwight,
Try: '================ Public Sub Tester() Dim WB As Workbook Dim SH As Worksheet Dim Rng As Range Dim rCell As Range Dim delRng As Range Dim iLastRow As Long Dim CalcMode As Long Set WB = Workbooks(ABook.xls) '<<===== CHANGE Set SH = WB.Sheets("Sheet2") '<<===== CHANGE With SH iLastRow = .Cells(Rows.Count, "A").End(xlUp).Row Set Rng = .Range("A2:A" & iLastRow) End With On Error GoTo XIT With Application CalcMode = .Calculation .Calculation = xlCalculationManual .ScreenUpdating = False End With For Each rCell In Rng.Cells With rCell If InStr(1, .Value, "MZ", vbTextCompare) = 0 Then If delRng Is Nothing Then Set delRng = rCell Else Set delRng = Union(rCell, delRng) End If End If End With Next rCell If Not delRng Is Nothing Then delRng.EntireRow.Delete End If XIT: With Application .Calculation = CalcMode .ScreenUpdating = True End With End Sub '<<================ --- Regards, Norman wrote in message oups.com... I have a macro which does the does the opposite of what I want. It deletes all the rows that contains MZ in the first column and leaves everything behing. I just want to keep the rows that contain MZ in the first column and delete all the others rows. Any help will be greatly appreciated. Here is what I have; Sub Delete_Row() Dim myWord As String myWord = "MZ" With ActiveSheet On Error Resume Next Do .Cells.Find(What:="MZ", After:=.Cells(.Cells.Count), _ LookIn:=xlFormulas, Lookat:=xlPart, _ SearchOrder:=xlByRows, SearchDirection:=xlNext, _ MatchCase:=True).EntireRow.Delete If Err.Number < 0 Then Exit Do Loop On Error GoTo 0 End With End Sub |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Marco to delete all rows except those containing 'MZ' in string
try this using col A
Sub Delete_Row() For i = Cells(Rows.Count, "a"). _ End(xlUp).Row To 2 Step -1 If InStr(UCase(Cells(i, "a")), "MZ") 0 _ Then Rows(i).Delete Next i End Sub -- Don Guillett SalesAid Software wrote in message oups.com... I have a macro which does the does the opposite of what I want. It deletes all the rows that contains MZ in the first column and leaves everything behing. I just want to keep the rows that contain MZ in the first column and delete all the others rows. Any help will be greatly appreciated. Here is what I have; Sub Delete_Row() Dim myWord As String myWord = "MZ" With ActiveSheet On Error Resume Next Do .Cells.Find(What:="MZ", After:=.Cells(.Cells.Count), _ LookIn:=xlFormulas, Lookat:=xlPart, _ SearchOrder:=xlByRows, SearchDirection:=xlNext, _ MatchCase:=True).EntireRow.Delete If Err.Number < 0 Then Exit Do Loop On Error GoTo 0 End With End Sub |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Marco to delete all rows except those containing 'MZ' in string
Oooooooooooops
If InStr(UCase(Cells(i, "a")), "MZ") 0 _ should be If InStr(UCase(Cells(i, "a")), "MZ")<1 _ -- Don Guillett SalesAid Software "Don Guillett" wrote in message ... try this using col A Sub Delete_Row() For i = Cells(Rows.Count, "a"). _ End(xlUp).Row To 2 Step -1 If InStr(UCase(Cells(i, "a")), "MZ") 0 _ Then Rows(i).Delete Next i End Sub -- Don Guillett SalesAid Software wrote in message oups.com... I have a macro which does the does the opposite of what I want. It deletes all the rows that contains MZ in the first column and leaves everything behing. I just want to keep the rows that contain MZ in the first column and delete all the others rows. Any help will be greatly appreciated. Here is what I have; Sub Delete_Row() Dim myWord As String myWord = "MZ" With ActiveSheet On Error Resume Next Do .Cells.Find(What:="MZ", After:=.Cells(.Cells.Count), _ LookIn:=xlFormulas, Lookat:=xlPart, _ SearchOrder:=xlByRows, SearchDirection:=xlNext, _ MatchCase:=True).EntireRow.Delete If Err.Number < 0 Then Exit Do Loop On Error GoTo 0 End With End Sub |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Marco to delete all rows except those containing 'MZ' in string
Hi Norman
Can you contact me private -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Norman Jones" wrote in message ... Hi Dwight, Try: '================ Public Sub Tester() Dim WB As Workbook Dim SH As Worksheet Dim Rng As Range Dim rCell As Range Dim delRng As Range Dim iLastRow As Long Dim CalcMode As Long Set WB = Workbooks(ABook.xls) '<<===== CHANGE Set SH = WB.Sheets("Sheet2") '<<===== CHANGE With SH iLastRow = .Cells(Rows.Count, "A").End(xlUp).Row Set Rng = .Range("A2:A" & iLastRow) End With On Error GoTo XIT With Application CalcMode = .Calculation .Calculation = xlCalculationManual .ScreenUpdating = False End With For Each rCell In Rng.Cells With rCell If InStr(1, .Value, "MZ", vbTextCompare) = 0 Then If delRng Is Nothing Then Set delRng = rCell Else Set delRng = Union(rCell, delRng) End If End If End With Next rCell If Not delRng Is Nothing Then delRng.EntireRow.Delete End If XIT: With Application .Calculation = CalcMode .ScreenUpdating = True End With End Sub '<<================ --- Regards, Norman wrote in message oups.com... I have a macro which does the does the opposite of what I want. It deletes all the rows that contains MZ in the first column and leaves everything behing. I just want to keep the rows that contain MZ in the first column and delete all the others rows. Any help will be greatly appreciated. Here is what I have; Sub Delete_Row() Dim myWord As String myWord = "MZ" With ActiveSheet On Error Resume Next Do .Cells.Find(What:="MZ", After:=.Cells(.Cells.Count), _ LookIn:=xlFormulas, Lookat:=xlPart, _ SearchOrder:=xlByRows, SearchDirection:=xlNext, _ MatchCase:=True).EntireRow.Delete If Err.Number < 0 Then Exit Do Loop On Error GoTo 0 End With End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Marco to Delete Worksheets | Excel Discussion (Misc queries) | |||
marco to delete rows | Excel Discussion (Misc queries) | |||
DELETE ROWS FROM XLS IF THE ROW CONTAINS THE STRING | Excel Discussion (Misc queries) | |||
How to write a marco in Excel to delete worksheet? | Excel Programming | |||
A Marco Delete Question | Excel Programming |