Macro Help - Delete Duplicated Data

[mccrimmon]

Hi

I was wondering if anyone could help with the following spreadsheet I
am trying to produce.

Basically, everyday I run a report showing volumes of trading (varies
every day, some days only a few trades, others there are hundreds)

Each trade has a unique transaction number.

Everyday we report the trades that have been confirmed within a four
day period. However, if some trades have been reported in the previous
days report, we do not want to duplicate it, therefor have to manually
delete it.

I was wondering if there was a macro that I could create so that I can
delete any data from the sheet named "NEW" that already appears on
sheets "Day1" "Day2" "Day3" "Day4"

Then after the first macro is run i can just paste the data from sheet
"NEW" into "Day 4" the data from Day 4 into sheet "Day 3" and so on
etc.

Any help would be greatly appreciated and I hope ive made some sense of
what im trying to achieve!

Cheers

Brian


+-------------------------------------------------------------------+
|Filename: NewTest.zip |
|Download: http://www.excelforum.com/attachment.php?postid=3661 |
+-------------------------------------------------------------------+

 

[Norman Jones]

Hi McCrimmon,

I was wondering if there was a macro that I could create so that I can
delete any data from the sheet named "NEW" that already appears on
sheets "Day1" "Day2" "Day3" "Day4"

See Chip Pearson's Tagging Duplicates page at:

http://www.cpearson.com/excel/duplicat.htm#TaggingDuplicates


See particularly, the section entitled: 'Extracting Values Common To Two
Lists'

 

[mccrimmon]

The problem is that Its only one column that is unique with its own
reference so a formula like that would work, however, i need to keep
all the other data in the spreadsheet aswell, just deleting any rows
that have already been previously reported.

Could anyone have a bash at this and see what they come up with?

Cheers

McCrimmon

 

[mccrimmon]

Anyone please? :

 

[mccrimmon]

pretty please

 

[Norman Jones]

Hi McCrimon,

Assume that the unique transaction numbers start in A2 on each sheet.

The following deletes all rows on New whose transaction number is found in
column A on any of the Day1, Day2, Day3 or Day4 sheets.

The Day1 sheet is then renamed "ArchiveCopy" with an appended date.

The following name changes are effecte:

Day4===> Day3
Day3===> Day2
Day2===>Day1

Finally, the New sheet is renamed Day4.

Test this on a *copy* of your workbook.

'===============================>>
Sub TestIt()

Dim Arr As Variant
Dim sh As Worksheet, sh2 As Worksheet
Dim Rng1 As Range, Rng2 As Range
Dim delRng As Range
Dim rCell As Range
Dim Lrow As Long
Dim lastRow As Long
Dim i As Long

Application.ScreenUpdating = False

Arr = Array("Day1", "Day2", "Day3", "Day4")

Set sh = ThisWorkbook.Sheets("New")

Lrow = sh.Cells(Rows.Count, "A").End(xlUp).Row

Set Rng1 = sh.Range("A2").Resize(Lrow - 1)

For Each rCell In Rng1.Cells
For i = LBound(Arr) To UBound(Arr)
Set sh2 = Sheets(Arr(i))
lastRow = sh2.Cells(Rows.Count, "A").End(xlUp).Row
Set Rng2 = sh2.Range("A1").Resize(lastRow)
If Not IsError _
(Application.Match(rCell.Value, Rng2, 0)) Then
If Not delRng Is Nothing Then
Set delRng = Union(rCell, delRng)
Else
Set delRng = rCell
End If
Exit For
End If
Next i
Next rCell

If Not delRng Is Nothing Then delRng.EntireRow.Delete

Sheets(Arr(0)).Name = "ArchiveCopy" & _
Format(Date, "yyyy-mm-dd")

For i = LBound(Arr) + 1 To UBound(Arr)
Sheets(Arr(i)).Name = Arr(i - 1)
Next i

Sheets("New").Name = Arr(UBound(Arr))

Application.ScreenUpdating = False

End Sub
'===============================>>

The bove code should be pated into a normal module in the worbook holding
the data sheets.

 

[mccrimmon]

with doing all of the above i seem to be getting a runtime error "9":
subscript out of range

The reference it relates to seems to be:

Set sh2 = Sheets(Arr(i))

Any suggestions?

 

[Dave Peterson]

Norman used this line:
Arr = Array("Day1", "Day2", "Day3", "Day4")

These are the worksheet names that his code expects to find.

If you don't have a worksheet named Day1, Day2, ...

Then you'll get that error.

So you can either rename sheets or adjust that line:
Arr = Array("Day1", "Day2", "Day3", "Day4")
to match your worksheet names.

 

[Norman Jones]

Hi McCrimmon,

The error indicates that one of the sheets whose names is included in:

Arr = Array("Day1", "Day2", "Day3", "Day4")

has not been found.

Check the names and, if necessary, look for initial, traling or intermrdiate
spaces.

If you check the value of i when the error occurs, it will point you to the
problematic sheet name. Bear in mind that the array is 0-based, so if the
error occurs when (say) i = 1, you should check for the presence of a sheet
named "Day2" (i.e, the 2nd array element). To check the value of i, set a
watch on it in the VBE.

I have a working test book if you would like it.

 

[Norman Jones]

Hi McCrimmon,

Dave has already responded to your problem but, unless my selective
blindness is even more pronounced than normal, it was not there when *I*
replied.

Sorry Dave!

 

[Dave Peterson]

No problem. There's always a time lag between loading the messages, reading,
and responding.

And I think it helps--sometimes it gives alternative suggestions to solve the
same challenge.