Delete upon finding

buntykins · May 2nd, 2002, 08:13 AM

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 · May 2nd, 2002, 08:23 AM

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 · May 2nd, 2002, 08:24 AM

Try this:

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

Ricky Morris · May 2nd, 2002, 09:49 AM

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 ]

May 2nd, 2002, 08:24 AM	#3
Ricky Morris Board Regular Join Date: Mar 2002 Posts: 363	Try this: Columns("A:A").Find(What:="COMPANY NA").EntireRow.Delete __________________ It's never too late to learn something new. Ricky

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

May 2nd, 2002, 08:13 AM	#1
buntykins Board Regular Join Date: Apr 2002 Posts: 76	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

May 2nd, 2002, 08:23 AM	#2
AJ Board Regular Join Date: Mar 2002 Location: =ActiveCell.Address Posts: 478	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

May 2nd, 2002, 09:49 AM	#4
Ricky Morris Board Regular Join Date: Mar 2002 Posts: 363	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 ]