Excel Macro not working with excel 2010

vijay2482

Board Regular
Joined
Mar 3, 2009
Messages
142
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.
 

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.
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:
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)
'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.
 
Upvote 0
You will get that error in any version of Excel if there are no cells of the type specified in the SpecialCells method.
 
Upvote 0
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:
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
 
Upvote 0
What's that Exit Sub doing in the middle of your procedure? Currently it appears that nothing is being deleted.
 
Upvote 0
If an error is encountered in the below line,
Code:
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.
 
Upvote 0
Like this?

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

Forum statistics

Threads
1,213,554
Messages
6,114,280
Members
448,562
Latest member
Flashbond

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top