Problem with Code

[Trevasaurus]

I have this code for comparing two columns, finding repeated entries, and deleting the row where the entry is repeated. I've tested it on a small data base (<50 entries) and it works. However, when I apply it to a larger database (>300), I'm getting an error: "Subscript Out of Range"

Sub find_copy01()
Dim c2 As Range, c As Range
With Sheets("Sheet2")
LR = .Cells.Find(What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
For Each c2 In .Range("A1:A" & LR)
Set c = Sheets("Sheet1").Columns(1).Find(c2, , xlValues, xlWhole)
If Not c Is Nothing Then
c.EntireRow.Interior.Color = vbRed
c2.EntireRow.Delete
End If
Next c2
End With
End Sub

Can anyone tell me what is wrong?

 

[Jerry Sullivan]

Trev,

The code worked for me in your workbook once I changed "Sheet1" to match your actual sheet name.

I didn't see my suggested macro in your file, so I don't know if you had been trying it with "Sheet1" or the actual sheet name.

 

[Trevasaurus]

Hey Jerry.

I was using "sheet1." It works fine now. Thanks for your help.

 

[Jerry Sullivan]

Trev,

Thanks for providing feedback to confirm the cause. I'm glad it was something easy to fix.

Referencing worksheets can be confusing when first working with VBA because there are several similar ways to do the same thing.

Some examples:

[B]Sheets("Sheet1").Select[/B]     ' This references the sheet that reads "Sheet1" (no quotes) on the tab - 
                                    regardless of what order it falls within all tabs.

[B]Sheets(1).Select  [/B]          ' This references the first sheet in the workbook - 
                                     regardless of its sheet name.

[B]Sheet1.Select[/B]               ' This references the CodeName for the Sheet-
                                     regardless of sheetname or order.
                                     CodeNames can be seen in the Properties Window of the VB Editor.