Marco to delete all rows except those containing 'MZ' in string

[dwight.yorke]

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

 

[Jim Jackson]

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

 

[Norman Jones]

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

 

[Gord Dibben]

Sub delete_rows()
Dim RowNdx As Long
Dim LastRow As Long
LastRow = ActiveSheet.UsedRange.Rows.Count
For RowNdx = LastRow To 1 Step -1
If Cells(RowNdx, "A").Value <> "MZ" Then
Rows(RowNdx).Delete
End If
Next RowNdx
End Sub


Gord Dibben MS Excel MVP

 

[Don Guillett]

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]

Oooooooooooops
If InStr(UCase(Cells(i, "a")), "MZ") > 0 _
should be
If InStr(UCase(Cells(i, "a")), "MZ")<1 _

 

[Ron de Bruin]

Hi Norman

Can you contact me private