vba code to delete duplicate rows in range causing error 1004

[vba317]

I am trying without success to write a vba procedure to delete a duplicate range of cells. The object of the procedure is to define one range of cells and if the code finds a match it should delete the range that matches. This worksheet was imported data from a text file.
The error is happening on the If Sheets("RawImport").Range(rng1).Value = rngFind Then line. Currently the code says the rngFind is Nothing but I know that a match exists on lines 460 - 478 on the RawImport Sheet. Any help is appreciated.

Dim lngLastRow As Long
Dim lngLoop1 As Long
Dim lngLoop2 As Long
Dim wrkbk As Excel.Workbook
Dim wrkShSrc As Excel.Worksheet
Dim wrkShDest As Excel.Worksheet
Dim lngRangeStart() As Variant
Dim lngRangeEnd() As Variant
Dim rng1 As Range
Dim rng2 As Range
Dim rng() As Variant
Dim rngFind As Range
Dim strRange As String
Dim strEnd1 As String


'On Error Resume Next


Set wrkbk = ActiveWorkbook
Set wrkShSrc = wrkbk.Worksheets("RawImport")
Set wrkShDest = wrkbk.Worksheets("Results")

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



 Set rng1 = Range("A190:A208")strRange = "A209:A" & lngLastRow


Set rngFind = wrkShSrc.Range("A209:A" & lngLastRow).Find(What:=strRange, LookIn:=xlValues, _
    LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
    MatchCase:=False, SearchFormat:=False)
    
    
If Sheets("RawImport").Range(rng1).Value = rngFind Then
    Sheets("RawImport").Range(rngFind).RemoveDuplicates Columns:=Array(1, 1), Header:=xlNo
    'Deletes all rows after A209
    Sheets("RawImport").Range(rngFind).EntireRow.Delete
End If

 

[JoeMo]

This is a duplicate thread.

 

[vba317]

Joe,
For some reason I can't see your original post here anymore. I added code to make sure that the sheet is activated and that did not help me. As far as I know this is not a duplicate thread.

 wrkbk.Worksheets("RawImport").Activate
'Rate Table Name: BASE PREMIUM
Set rng1 = wrkShSrc.Range("A190:A208")
strRange = "A209:A" & lngLastRow






Set rngFind = wrkShSrc.Range("A209:A" & lngLastRow).Find(What:=strRange, LookIn:=xlValues, _
    LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
    MatchCase:=False, SearchFormat:=False)
    
    
If wrkShSrc.Range(rng1).Value = rngFind Then
    wrkShSrc.Range(rngFind).RemoveDuplicates Columns:=Array(1, 1), Header:=xlNo
    'Deletes all rows after A209
    wrkShSrc.Range(rngFind).EntireRow.Delete

 

[JoeMo]

Joe,
For some reason I can't see your original post here anymore. I added code to make sure that the sheet is activated and that did not help me. As far as I know this is not a duplicate thread.

 wrkbk.Worksheets("RawImport").Activate
'Rate Table Name: BASE PREMIUM
Set rng1 = wrkShSrc.Range("A190:A208")
strRange = "A209:A" & lngLastRow






Set rngFind = wrkShSrc.Range("A209:A" & lngLastRow).Find(What:=strRange, LookIn:=xlValues, _
    LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
    MatchCase:=False, SearchFormat:=False)
    
    
If wrkShSrc.Range(rng1).Value = rngFind Then
    wrkShSrc.Range(rngFind).RemoveDuplicates Columns:=Array(1, 1), Header:=xlNo
    'Deletes all rows after A209
    wrkShSrc.Range(rngFind).EntireRow.Delete

Here's a link to the duplicate post that was posted prior to this thread's inception.

http://www.mrexcel.com/forum/excel-...uplicate-rows-range-causing-error-1004-a.html

 

[vba317]

Joe,
After doing more research I am thinking that maybe what I am trying to do is not possible. My goal was to compare a range of cells to another range of cells and if duplicates are found to delete the duplicates. All the code I have found on the net has been to compare one row of data to another row. In my first example cells A190 - cells A208 match cells A460 - A478, so I would like to delete cells A460-A478. I started using if statements and coded deleting specific rows. But this became a nightmare keeping track of what row number to change, because of course the row numbers change once a set is deleted. So in my rngFind object I am setting it to check all the rows below the rows where the original range is set. So in this example it would be checking A209 - A3500.
Is this the correct approach?
Tom

 

[JoeMo]

Delete from the bottom up. When you delete a row, the rows above it do not change.