Delete upon finding

[buntykins]

How do I do this in VBA? I want it look down column A and if there is an cell with "COMPANY NA" in it, I need it to delete the entire row

How do I do this?

Janie

 

[AJ]

Hiya,

Here's some sample code for you to play with. It may not be the most efficient way of doing what you ask but it does work.
Sub test()

Dim LastRow As Long

LastRow = Range("A65536").End(xlUp).Row

Range("A1").Select

Do While ActiveCell.Row <= LastRow

If ActiveCell.Value = "COMPANY NA" Then

Rows(ActiveCell.Row & ":" & ActiveCell.Row).Delete Shift:=xlUp
LastRow = LastRow - 1
End If
ActiveCell.Offset(1, 0).Select
Loop

End Sub


Regards
AJ

 

[Ricky Morris]

Try this:

Columns("A:A").Find(What:="COMPANY NA").EntireRow.Delete

 

[Ricky Morris]

This will delete all the occurences of "Company NA"

Sub Macro1()
On Error Resume Next
Err.Clear
Do While Err.Description<> "Object variable or With block variable not set"
Columns("A:A").Find(What:="COMPANY NA").EntireRow.Delete
Loop
End Sub


_________________
It's never too late to learn something new.

Ricky
This message was edited by Ricky Morris on 2002-05-02 08:49