PDA

View Full Version : delete rows based on search criteria



Rem0ram
07-31-2014, 02:04 AM
Attached the sample file.

requirement

1. have list of mailds available in sheet mDUM
2. need to search those ids in sheet mAOU and if the id is available then delete the entire row and this needs to be repeated to end of row in mDUM
3. once all the matching rows deleted then all the rows in mDUM to be moved to mROU sheet.

any help will be helpful.


Best
Rem0$

westconn1
07-31-2014, 05:11 AM
test this to see if it works for you

Set looksht = Sheets("maou")
Set listsht = Sheets("mdum")
lastrow = listsht.Cells(listsht.Rows.Count, 1).End(xlUp).Row
Set rngtodelete = listsht.Cells(lastrow + 1, 1).EntireRow
For Each cel In listsht.Range("a1:a" & lastrow).Cells
Set fnd = looksht.Range("a:a").Find(cel)
If Not fnd Is Nothing Then Set rngtodelete = Union(rngtodelete, cel.EntireRow)
Next
rngtodelete.Delete
Set rngtomove = listsht.Range("a1").Resize(listsht.Rows.Count, 1).End(xlUp).EntireRow
If rngtomove.Cells.Count > 0 Then
Set movesht = Sheets("mrou")
nextrow = movesht.Cells(movesht.Rows.Count, 1).End(xlUp).Row + 1
movesht.Cells(nextrow, 1).Resize(rngtomove.Rows.Count).EntireRow = rngtomove.Value
rngtomove.Delete
End If