MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Deleting A Line


Posted by Optionman on January 09, 2002 4:41 PM

In a macro, is there a way to delete a line if a
certain condition is met? Example, I want to look at
rows 1-25, and delete those where column a contains
"zzz". Any help would be appreciated.


Posted by Jacob on January 09, 2002 5:50 PM

Hi

Sub DeleteEmAll()

range("A1").select
Do
if activecell.value = "zzz" then
Rows(activecel.row).Select
Selection.Delete Shift:=xlUp
else
activecell.offset(1,0).select
loop while activecell.row <26<br>end sub

HTH

Jacob

Posted by Bagsy on January 09, 2002 7:15 PM

If your machine has problems with the do ... while commands, try this. Please note it will go to the last row in the column. To limit this, change the "To" value from lastrow to 25.

Private Sub DeleteZZZ()

Range("A1").Select
Selection.End(xlDown).Select
Rows(ActiveCell.Row).Select

Dim lastrow
lastrow = (ActiveCell.Row)
Range("a1").Select
For x = 1 To lastrow
If ActiveCell.Value = "zzz" Then
Rows(ActiveCell.Row).Select
Selection.Delete Shift:=xlUp
Else
ActiveCell.Offset(1, 0).Select
End If
Next x
End Sub