Sam,
Assuming your values of interest are in columns A and B, with headers in
row1, then the macro below will show any duplicates.
HTH,
Bernie
MS Excel MVP
Sub ShowSamHisDuplicates()
Application.ScreenUpdating = False
Range("A:B").EntireColumn.Insert
Range("A1").Value = "Key"
Range("B1").Value = "Count"
Range("A2").FormulaR1C1 = "=RC[2]&RC[3]"
Range("B2").FormulaR1C1 = "=COUNTIF(C[-1],RC[-1])"
Range("A2:B2").AutoFill _
Destination:=Range("A2:B" & Range("C65536").End(xlUp).Row)
Range("B:B").AutoFilter Field:=1, Criteria1:="1"
If Range("B1").CurrentRegion.Columns(2).SpecialCells _
(xlCellTypeVisible).Cells.Count 1 Then
Application.ScreenUpdating = True
MsgBox "There are duplicated values"
Else
Range("A:B").EntireColumn.Delete
Application.ScreenUpdating = True
MsgBox "There were no duplicated values"
End If
End Sub
"Sam" wrote in message
...
Hi - I Hope that somebody may be able to help me
I need to highlight duplicate rows in a spreadsheet using
vba and to display a message box indicating that there are duplicated rows
and that a reveiw is required prior to import
The spreadsheet is called Data_01.xls
The potentially duplicated rows relate to columns named
OrderNo : ProductNo : Qty Company
Each row contains an order number with a product number and a quantity
The duplicate rows are usually products ordered twice, not always with the
same quantities, against the same order number
I would be grateful for any help
Sam
|