deleting rows with a macro

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

hi,
I am using Excel 2000 and would like to create a macro to delete rows that
do not contain specific text in column 1. so, if the cell does not contain
the words 'Centre' or 'Member' I would like to delete the row. I am a bit of
a beginner to macros and have looked up the microsoft web-pages but couldn't
quite see what I was looking for. Any help would be appreciated.
 
One way:

Public Sub DeleteNonCentreMemberRows()
Dim rCell As Range
Dim rDelete As Range

For Each rCell In Range("A1:A" & _
Range("A" & Rows.Count).End(xlUp).Row)
With rCell
If Not (LCase(.Text) = "centre" Or _
LCase(.Text) = "member") Then
If rDelete Is Nothing Then
Set rDelete = .Cells
Else
Set rDelete = Union(rDelete, .Cells)
End If
End If
End With
Next rCell
If Not rDelete Is Nothing Then rDelete.EntireRow.Delete
End Sub
 
Try this idea
Sub Deleteallrowsbut()
With Range("A3:A12")
.AutoFilter
.AutoFilter Field:=1, Criteria1:="<>b", _
Operator:=xlAnd, Criteria2:="<>other"
.EntireRow.Delete
End With
End Sub
 
Back
Top