run-time error 9
 

With the workbook open I get error at noted spots. I can
get rid of error at iListCount by making it equal to 129.
Also what about "x" not being Dim?
Thanks
Rick

Sub Compare2ListDeleteDupItems()
Dim iListCount As Integer '????, x As Variant
Dim iCtr As Integer

'Turn off screen upgrading to speedup maco
Application.ScreenUpdating = False

'Get count of records to search through (list that will
be deleted)
iListCount = Sheets("Sheet2").Range("A2:A130").Rows.Count
'Causes Run Time Error '9' <<Subscript out of range

'Loop through the MASTER list
For Each x In Sheets("Sheet1").Range("A2:A130")
'Causes Run Time Error '9'<<Subscript out of range
'Loop through all records in second list
For iCtr = 1 To iListCount
'Do comparison of next record
'To specify a different column, change 1 to the
column number
If x.Value = Sheets("Sheet2").Cells(iCtr, 1).Value
Then
'If match is true then delete row
Sheets("Sheet2").Cells(iCtr, 1).Delete x1Shiftup
'Increment counter to account for deleted row
iCtr = iCtr + 1
End If
Next iCtr
Next
Appliication.ScreenUpdating = True
MsgBox "DONE"

End Sub

run-time error 9
 

Do you have any sheets called Sheet1 and Sheet2?

Are you sure the correct workbook is active? Try qualifying with the
workbook object.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"RickM" wrote in message
...
With the workbook open I get error at noted spots. I can
get rid of error at iListCount by making it equal to 129.
Also what about "x" not being Dim?
Thanks
Rick

Sub Compare2ListDeleteDupItems()
Dim iListCount As Integer '????, x As Variant
Dim iCtr As Integer

'Turn off screen upgrading to speedup maco
Application.ScreenUpdating = False

'Get count of records to search through (list that will
be deleted)
iListCount = Sheets("Sheet2").Range("A2:A130").Rows.Count
'Causes Run Time Error '9' <<Subscript out of range

'Loop through the MASTER list
For Each x In Sheets("Sheet1").Range("A2:A130")
'Causes Run Time Error '9'<<Subscript out of range
'Loop through all records in second list
For iCtr = 1 To iListCount
'Do comparison of next record
'To specify a different column, change 1 to the
column number
If x.Value = Sheets("Sheet2").Cells(iCtr, 1).Value
Then
'If match is true then delete row
Sheets("Sheet2").Cells(iCtr, 1).Delete x1Shiftup
'Increment counter to account for deleted row
iCtr = iCtr + 1
End If
Next iCtr
Next
Appliication.ScreenUpdating = True
MsgBox "DONE"

End Sub