finding records

  • Thread starter Thread starter delvie
  • Start date Start date
D

delvie

I have a list of records and I am interested in only the records with a
specific name. How can I delete all of the records without the name.

Example:
apple street
orange steeet
green street
yellow street
green street west
green street east
apple street west
west green street
west apple street

I only want the records that have green in name and delete all others

Thanks
 
Try this

with the list in column A

Sub test()
Application.ScreenUpdating = False
Range("B1").EntireColumn.Insert
Dim FoundCell As Range
Dim firstAddress As String
With Range("A:A")
Set FoundCell = .Find(What:="green", LookAt:=xlPart)
If Not FoundCell Is Nothing Then
firstAddress = FoundCell.Address
Do
FoundCell.Offset(0, 1).Value = "x"
Set FoundCell = .FindNext(FoundCell)
Loop While Not FoundCell Is Nothing And FoundCell.Address <> firstAddress
End If
End With
On Error Resume Next 'In case there are no blank rows
Columns("B").SpecialCells(xlCellTypeBlanks).EntireRow.Delete
On Error GoTo 0
Columns("B").Delete
Application.ScreenUpdating = True
End Sub
 
A non-macro approach.

Apply Data|filter|Autofilter to your range.

Then use the dropdown and select custom
Does not contain: Green
Delete those visible rows.
data|filter|autofilter to get rid of that filter.
 
I think the former answer using the filter/sort is a lot
simpler, you idiots with your VBA make me vomit!
-----Original Message-----
Try this

with the list in column A

Sub test()
Application.ScreenUpdating = False
Range("B1").EntireColumn.Insert
Dim FoundCell As Range
Dim firstAddress As String
With Range("A:A")
Set FoundCell = .Find(What:="green", LookAt:=xlPart)
If Not FoundCell Is Nothing Then
firstAddress = FoundCell.Address
Do
FoundCell.Offset(0, 1).Value = "x"
Set FoundCell = .FindNext(FoundCell)
Loop While Not FoundCell Is Nothing And
Click to expand...
FoundCell.Address said:
End If
End With
On Error Resume Next 'In case there are no blank rows
(xlCellTypeBlanks).EntireRow.Delete
On Error GoTo 0
Columns("B").Delete
Application.ScreenUpdating = True
End Sub



--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2002 SP-2)




"delvie" <[email protected]> wrote in
Click to expand...
message news:[email protected]...
 
Sometimes VBA makes very good sense. If I had to do this each morning (or more
than once), I'd try to incorporate into code.

(from another VBA idiot!)
 
Back
Top