I do not know why this code is not running correctly when I paste a list of values into one column and another set of values
into the 2nd column. If I manualy enter data into column 1 and 2 and there are duplicate data then this vba code works but
when pasting from two different sources then I get error. I thought it was the formatting but as you can see I placed vba
code to remove all formating (or at least I thought I did). If someone can assist me, all I need is a vba code that will
compare two columns, delete duplicates, and sort all done in the 2nd Column. attached is my file and code listed below.
Public flag As Integer
Dim LR As Long, LC As Long
If flag = 1 Then Exit Sub [B] 'in order to run macro once[/B]
Application.ScreenUpdating = False
Range("A2:B6000").Font.Bold = False [B] 'clean format[/B]
Range("A2:B6000").Font.Italic = False [B] 'clean format
[/B] Range("A2:B6000").HorizontalAlignment = xlGeneral 'align values
Range("A2:B6000").VerticalAlignment = xlTop 'align values
Range("A2:B6000").WrapText = False [B] 'do not wrap text[/B]
Selection.PasteSpecial Paste:=xlPasteValues, _
.VerticalAlignment = xlTop
.WrapText = False
Range("A2:A6000").NumberFormat = "0000000000" [B] 'all values must in 10 digit format including zeros[/B]
Range("B2:B6000").NumberFormat = "0000000000" [B] 'same as above[/B]
LC = Range("A1").SpecialCells(xlCellTypeLastCell).Column + 5
LR = Range("B" & Rows.Count).End(xlUp).Row
Cells(1, LC) = "Key"
Range(Cells(2, LC), Cells(LR, LC)).FormulaR1C1 = "=ISNUMBER(MATCH(RC2,C1,0))"
Cells(1, LC).AutoFilter Field:=1, Criteria1:="True"
Range("B2:B" & LR).SpecialCells(xlCellTypeVisible).ClearContents [B] 'I receive an error on this line[/B]
Range("B2:B" & LR).Sort [B1], xlAscending
Application.ScreenUpdating = True
MsgBox LR - Range("B" & Rows.Count).End(xlUp).Row & " items were deleted"
flag = 1 [B] 'in order to run macro once, 2nd time seems to mess things up[/B]
If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines