Hi,
I got 2 worksheets containing data of students. what i want to do is,
find all the data of worksheet1 in worksheet 2, and delete entire row
containing all the data. e.g.

worksheet 1:
name ID No
NIK ANIS AMIRAH BINTI NIK IZANI 871007145768
NIK HIDAYATUL IZNI BINTI MOHD HATTA 870515105020
NIK IKHWAN HAQIM BIN HANAFI 871106295171
NIK MOHAMAD FAIDHI BIN NIK AZLAN 870206295481
NIK NUR AMALINA BINTI MAT ZAIDAN 870709035606
NIK SAIFUL LIZAM BIN NIK MOKHTAR 880415065315

i want to find all of these students in worksheet 2 and delete them
(entire row).

so any idea how to do that instead of using find & replace 1 by 1? for
info, worksheets 2 containing more than 2000 students data.

thanks.


--
sulie
------------------------------------------------------------------------
sulie's Profile: http://www.excelforum.com/member.php...o&userid=24063
View this thread: http://www.excelforum.com/showthread...hreadid=376980

There is no simple macro solution to your problem. This is because, the
deletions could occur hapzardly anywhere in the data range on Sheet2
hence making a looping step-through a nightmare. I take it that there
is no seqential similiarity in the structure of both lists.

One way around is to copy list on Sheet1 to say columns d and e on
Sheet2 and use a VLOOKUP whksheet function in column F to identify
repeats and non-repeats. In your IF clause, assign "" to non-repeats.
Next run a macro to delete rows with null strings "".

Sub delrows()
For each c in Range("F:F")
If c="" then c.entirerow.delete
end if
next
end if

HTH


--
davidm
------------------------------------------------------------------------
davidm's Profile: http://www.excelforum.com/member.php...o&userid=20645
View this thread: http://www.excelforum.com/showthread...hreadid=376980

Hi Sulie

Supposing the numbers identify the students and supposing that they ar
in column G both on sheet1 and sheet2 the macro below could solve you
problem:

Sub DeleteRowsOnSheet2()
Dim lRow As Long, lMaxRow As Long
Dim vRow As Variant
Dim sht1 As Worksheet, sht2 As Worksheet

Set sht1 = Worksheets(1)
Set sht2 = Worksheets(2)

' make things run faster
Application.ScreenUpdating = False

' thx to david mcritchie <g
lMaxRow = sht1.Cells(Rows.Count, 7).End(xlUp).Row

For lRow = 1 To lMaxRow
' if we have a number on this row in column G
If Not IsEmpty(sht1.Cells(lRow, 7)) Then
' try to find it on sheet 2 in column G
vRow = Application.Match(sht1.Cells(lRow, 7), sht2.Columns(7)
0)
If Not IsError(vRow) Then
' the number is there, so delete the row
sht2.Rows(vRow).Delete
End If
End If
Next lRow

MsgBox "Done!"

Set sht1 = Nothing
Set sht2 = Nothing
End Sub

With kind regards,
Ton Teun

--
Ton
-----------------------------------------------------------------------
TonT's Profile: http://www.officehelp.in/member.php?userid=4
View this thread: http://www.officehelp.in/showthread.php?t=66124
Visit - http://www.officehelp.in/archive/index.php | http://www.officehelp.in/index/index.ph