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

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

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

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

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

On 2 Apr 2007 12:35:07 -0700, 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

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

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

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