Hi All. I have two lists. the first is the "master" and the second is
the extracted. I would like to know the vba code to delete the
extracted from the master list.
Each list has two columns. the first contains the date and the second
contains a unique ID number.
The extracted list is updated everyday and I want the master list to
become shorter and shorter. any ideas?
thanks in anticipation!

Regards

Aaron Stockley


--
astockley2005
------------------------------------------------------------------------
astockley2005's Profile: http://www.excelforum.com/member.php...o&userid=24929
View this thread: http://www.excelforum.com/showthread...hreadid=384648

Hi Aaron,

Assume your two ranges are named Master and Extract, try:

Sub Tester()
Dim rCell As Range
Dim rng1 As Range
Dim rng2 As Range

Application.ScreenUpdating = False

Set rng1 = Range("Master")
Set rng2 = Range("Extracted")

For Each rCell In rng1.Columns(2).Cells
If Not IsError(Application.Match _
(rCell.Value, rng2.Columns(2).Cells, 0)) Then
rng1.Resize(1, rng1.Columns.Count).Delete shift:=xlUp
End If
Next
Application.ScreenUpdating = True
End Sub


---
Regards,
Norman



"astockley2005"
wrote in message
news:astockley2005.1rpq6u_1120594032.0823@excelfor um-nospam.com...

Hi All. I have two lists. the first is the "master" and the second is
the extracted. I would like to know the vba code to delete the
extracted from the master list.
Each list has two columns. the first contains the date and the second
contains a unique ID number.
The extracted list is updated everyday and I want the master list to
become shorter and shorter. any ideas?
thanks in anticipation!

Regards

Aaron Stockley


--
astockley2005
------------------------------------------------------------------------
astockley2005's Profile:
http://www.excelforum.com/member.php...o&userid=24929
View this thread: http://www.excelforum.com/showthread...hreadid=384648

Hi Aaron,

Please replace my sub with the following:

Sub Tester()
Dim rCell As Range
Dim rng1 As Range
Dim rng2 As Range
Dim RngDel As Range

Application.ScreenUpdating = False

Set rng1 = Range("Master")
Set rng2 = Range("Extracted")

For Each rCell In rng1.Columns(2).Cells
If Not IsError(Application.Match _
(rCell.Value, rng2.Columns(2).Cells, 0)) Then
If Not RngDel Is Nothing Then
Set RngDel = Union(RngDel, rCell. _
Resize(1, rng1.Columns.Count))
Else
Set RngDel = rCell.Resize(1, rng1.Columns.Count)
End If
End If
Next
RngDel.Delete shift:=xlUp

Application.ScreenUpdating = True

End Sub

The previous sub would fail because sequential deletions of this kind should
either operate from the bottom upwards or, as above, performing a mass
deletion at the end.

---
Regards,
Norman



"Norman Jones" wrote in message
...
Hi Aaron,

Assume your two ranges are named Master and Extract, try:

Sub Tester()
Dim rCell As Range
Dim rng1 As Range
Dim rng2 As Range

Application.ScreenUpdating = False

Set rng1 = Range("Master")
Set rng2 = Range("Extracted")

For Each rCell In rng1.Columns(2).Cells
If Not IsError(Application.Match _
(rCell.Value, rng2.Columns(2).Cells, 0)) Then
rng1.Resize(1, rng1.Columns.Count).Delete shift:=xlUp
End If
Next
Application.ScreenUpdating = True
End Sub


---
Regards,
Norman



"astockley2005"
wrote in
message news:astockley2005.1rpq6u_1120594032.0823@excelfor um-nospam.com...

Hi All. I have two lists. the first is the "master" and the second is
the extracted. I would like to know the vba code to delete the
extracted from the master list.
Each list has two columns. the first contains the date and the second
contains a unique ID number.
The extracted list is updated everyday and I want the master list to
become shorter and shorter. any ideas?
thanks in anticipation!

Regards

Aaron Stockley


--
astockley2005
------------------------------------------------------------------------
astockley2005's Profile:
http://www.excelforum.com/member.php...o&userid=24929
View this thread:
http://www.excelforum.com/showthread...hreadid=384648

Hi Aaron,

Correcting another problem, use instead :

Sub Tester()
Dim rCell As Range
Dim rng1 As Range
Dim rng2 As Range
Dim RngDel As Range

Application.ScreenUpdating = False

Set rng1 = Range("Master")
Set rng2 = Range("Extracted")

For Each rCell In rng1.Columns(2).Cells
If Not IsError(Application.Match _
(rCell.Value, rng2.Columns(2).Cells, 0)) Then
If Not RngDel Is Nothing Then
Set RngDel = Union(RngDel, rCell.Offset(, -1). _
Resize(1, rng1.Columns.Count))
Else
Set RngDel = rCell.Offset(, -1). _
Resize(1, rng1.Columns.Count)
End If
End If
Next
If Not RngDel Is Nothing Then RngDel.Delete shift:=xlUp
Application.ScreenUpdating = True
End Sub


---
Regards,
Norman

Hey Norman!
Thanks for the help! That was really starting to do my head in!!
Another one for ya though....

How can I email a workbook? Simple you may say but I want it to go to
the same person each time and I do not want the user to have to select
this from the outlook contact list.

Regards

Aaron Stockley


--
astockley2005
------------------------------------------------------------------------
astockley2005's Profile: http://www.excelforum.com/member.php...o&userid=24929
View this thread: http://www.excelforum.com/showthread...hreadid=384648

Hi Aaron,

Thanks for the help!

You are very welcome

How can I email a workbook? Simple you may say but I want it to go to
the same person each time and I do not want the user to have to select
this from the outlook contact list.

Visit Ron de Bruin's web site and look at his SendMail pages. These comprise
an extensive range of procedures to effect a wide range of email activity.

While at Ron's site, look also at his SendMail addin.

http://www.rondebruin.nl/sendmail.htm


---
Regards,
Norman



"astockley2005"
wrote in message
news:astockley2005.1rrnmr_1120683982.6379@excelfor um-nospam.com...

Hey Norman!
Thanks for the help! That was really starting to do my head in!!
Another one for ya though....

How can I email a workbook? Simple you may say but I want it to go to
the same person each time and I do not want the user to have to select
this from the outlook contact list.

Regards

Aaron Stockley


--
astockley2005
------------------------------------------------------------------------
astockley2005's Profile:
http://www.excelforum.com/member.php...o&userid=24929
View this thread: http://www.excelforum.com/showthread...hreadid=384648