Excel Macro not working with excel 2010

[vijay2482]

Hi all,

I have a excel macro written in excel 2003.
This macro is run from my vb.net application.
The macro runs successfully on excel 2003 & 2007 but with excel 2010, I get the below error message:
Execution error:1004
No corresponding cells.

Not sure why this is happening...
Any suggestion, advic,solution will be helpful.

Thanks in advance.

 

[Andrew Poulsom]

What line of code is causing that error?

 

[vijay2482]

Thanks for the reply.
Currently I have 2007 on my PC.
I will check on another pc that has 2010 for the exact line of code that produces the error.

The below is the code that produces the error:

Sub DeleteDuplicateOrangeX()
'Compare columns G,L,M & W for duplicated rows, delete the ones that
'are duplicates AND have an orange color in column X
Dim LR As Long, Rng As Range, Cell As Range
Application.ScreenUpdating = False
LR = Range("A" & Rows.Count).End(xlUp).Row
'Create key columns to evaluate columns of data
    Range("CC5:CC" & LR).FormulaR1C1 = "=RC7&""-""&RC12&""-""&RC13&""-""&RC23"
    Range("CD5:CE" & LR).FormulaR1C1 = "=COUNTIF(C81,RC81)>1"
    Range("CC5:CE" & LR).Value = Range("CC5:CE" & LR).Value
    Range("CD3") = "key"
 
'Autofilter duplicated rows
    Range("CD3:CE" & LR).AutoFilter Field:=1, Criteria1:=True
    Set Rng = Range("CD5:CE" & LR).SpecialCells(xlCellTypeVisible)
'Flag duplicates with orange "X" columns
    For Each Cell In Rng
        If Cells(Cell.Row, "X").Interior.ColorIndex = 44 Then Cell = 1
    Next Cell
 
'Refilter by flagged rows and delete
    Range("CD3:CE" & LR).AutoFilter Field:=1, Criteria1:=1
    If Range("CE" & Rows.Count).End(xlUp).Row > 4 Then _
    Range("A5:A" & LR).SpecialCells(xlCellTypeVisible).EntireRow.Delete xlShiftUp
'Cleanup
ActiveSheet.AutoFilterMode = False
Range("CC:CE").ClearContents
Rows(4).Hidden = True
Application.ScreenUpdating = True
End Sub

I remember the error was produced on the range line.

 

[Andrew Poulsom]

You will get that error in any version of Excel if there are no cells of the type specified in the SpecialCells method.

 

[vijay2482]

Then how to solve this issue?
I'm not clear about your statement.

Thanks for the reply.

 

[Andrew Poulsom]

You will have to trap the error.

 

[vijay2482]

I used the on error to handle the error.
This eliminated the error, but I found that the output was wrong.
The code has to remove lines that have duplicate lines with values in columns 7,12,13, and 23 along with orange color in the cell of a row.
But it remove the duplicates along with the original(or one row that has to be kept in the excel)

Code:

Sub DeleteDuplicateOrangeX()
'Compare columns G,L,M & W for duplicated rows, delete the ones that
'are duplicates AND have an orange color in column X
Dim LR As Long, Rng As Range, Cell As Range
Application.ScreenUpdating = False
LR = Range("A" & Rows.Count).End(xlUp).Row
'Create key columns to evaluate columns of data
    Range("CC5:CC" & LR).FormulaR1C1 = "=RC7&""-""&RC12&""-""&RC13&""-""&RC23"
    Range("CD5:CE" & LR).FormulaR1C1 = "=COUNTIF(C81,RC81)>1"
    Range("CC5:CE" & LR).Value = Range("CC5:CE" & LR).Value
    Range("CD3") = "key"
    
'Autofilter duplicated rows
    Range("CD3:CE" & LR).AutoFilter Field:=1, Criteria1:=True
    'Set Rng = Range("CD5:CE" & LR).SpecialCells(xlCellTypeVisible)
    On Error Resume Next
    Set Rng = Range("CD5:CE" & LR).SpecialCells(xlCellTypeVisible)
   Exit Sub
'Flag duplicates with orange "X" columns
    For Each Cell In Rng
        If Cells(Cell.Row, "X").Interior.ColorIndex = 44 Then Cell = 1
    Next Cell
    
'Refilter by flagged rows and delete
    Range("CD3:CE" & LR).AutoFilter Field:=1, Criteria1:=1
    If Range("CE" & Rows.Count).End(xlUp).Row > 4 Then _
        Range("A5:A" & LR).SpecialCells(xlCellTypeVisible).EntireRow.Delete xlShiftUp
'Cleanup
ActiveSheet.AutoFilterMode = False
Range("CC:CE").ClearContents
Rows(4).Hidden = True
Application.ScreenUpdating = True
End Sub

 

[Andrew Poulsom]

What's that Exit Sub doing in the middle of your procedure? Currently it appears that nothing is being deleted.

 

[vijay2482]

If an error is encountered in the below line,

Set Rng = Range("CD5:CE" & LR).SpecialCells(xlCellTypeVisible)

On error resume next will be followed by the exit sub line.I want to exit the sub, without checking the other lines of code.

If I'm wrong, then how to proceed?
Thanks for any help.

 

[Andrew Poulsom]

Like this?

On Error Resume Next
Set Rng = Range("CD5:CE" & LR).SpecialCells(xlCellTypeVisible)
If Err <> 0 Then Exit Sub
On Error GoTo 0