delete rows

  • Thread starter Thread starter Yvonne Groeneveld
  • Start date Start date
Y

Yvonne Groeneveld

Hello,

I would like to have code for the following action:

"delete all rows in which the cell in col B is xxx and the cell in col D is
yyy"

if there are no such rows or after the deleting

"delete all rows in which the cell in col C is aaa and the cell in col D is
zero or blank"

TIA

Yvonne
 
This worked for me in xl2002:

Option Explicit
Sub testme02()

Dim FirstRow As Long
Dim LastRow As Long
Dim iRow As Long
Dim dummyRng As Range
Dim wks As Worksheet

Set wks = Worksheets("sheet1")
With wks
Set dummyRng = .UsedRange 'try to reset usedrange
FirstRow = 1
LastRow = .Cells.SpecialCells(xlCellTypeLastCell).Row

For iRow = LastRow To FirstRow Step -1
If (StrComp(.Cells(iRow, "B").Value, "xxx", vbTextCompare) = 0 _
And StrComp(.Cells(iRow, "D").Value, "yyy", vbTextCompare) = 0) _
Or ((.Cells(iRow, "D").Value = "" Or .Cells(iRow, "D").Value = 0) _
And StrComp(.Cells(iRow, "C").Value, "aaa", vbTextCompare) = 0) Then
.Rows(iRow).Delete
End If
Next iRow
End With

End Sub
 
Back
Top