Hi Bruno,

Thanks for the info. I have never used Visual basic before so have copied
and pasted your work and then changed the data ranges.

Unable to run it - keep getting 'Syntax error' on Sub ComparingData () line.

It's obviously beyond me but thanks so much for trying.



"Bruno Campanini" wrote:

> "Minuette" <Minuette@discussions.microsoft.com> wrote in message
> news:87CA6665-92A0-42CF-A811-D72E1AC6A42D@microsoft.com...
> >I need to compare a new worksheet to a very large master worksheet and
> > generate a list of names which are missing from the master worksheet. Is
> > there a function that will help me do this?
>
> Here is one, supply your ranges in Definitions (initial cells only):
>
> =========================
> Sub ComparingData()
> Dim CompareColl As New Collection
> Dim MissingFromMaster As New Collection
> Dim SourceRange As Range
> Dim CompareToRange As Range
> Dim TargetRange As Range, i
>
> ' Definitions
> ' --------------------------------------------------
> Set SourceRange = [Sheet10!W70]
> Set CompareToRange = [NameSheet!I32]
> Set TargetRange = [Sheet10!X70]
> '---------------------------------------------------
>
> Set SourceRange = Range(SourceRange, SourceRange.End(xlDown))
> Set CompareToRange = Range(CompareToRange, CompareToRange.End(xlDown))
> Application.Calculation = xlCalculationManual
> Application.ScreenUpdating = False
>
> For Each i In SourceRange
> On Error Resume Next
> CompareColl.Add i, i
> Next
> For Each i In CompareToRange
> On Error GoTo MissingName
> CompareColl.Add i, i
> On Error Resume Next
> MissingFromMaster.Add i, i
> Continue:
> Next
> On Error GoTo 0
>
> For i = 1 To MissingFromMaster.Count
> TargetRange(i) = MissingFromMaster(i)
> Next
>
> Exit_Sub:
> Application.Calculation = xlCalculationAutomatic
> Application.ScreenUpdating = True
> Exit Sub
>
> MissingName:
> Resume Continue
>
> End Sub
> ===========================
>
> Let me know how things go.
> Ciao, Bruno
>
>
>